3 tables query

I hable that has a ProOnwerID and ProFinOwner, when I create the following
view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
ProOwer shows up.. Any Idea why ?... is it because I have two ID for
Clients en same table ?
SELECT dbo.projects.*, dbo.employee.EmpName AS EmpName,
dbo.Clients.ClientName AS ProOwner, dbo.Clients.ClientName AS FinOwner
FROM dbo.projects LEFT OUTER JOIN
dbo.employee ON dbo.projects.ProPMID =
dbo.employee.EmpUserID LEFT OUTER JOIN
dbo.Clients ON dbo.projects.ProFinOwner =
dbo.Clients.ClientId AND dbo.projects.ProOwner = dbo.Clients.ClientId
thanksOn Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:

>I hable that has a ProOnwerID and ProFinOwner, when I create the following
>view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
>ProOwer shows up.. Any Idea why ?... is it because I have two ID for
>Clients en same table ?
Hi Carlos,
Your query tries to find ONE row in Clients that is equal to both the
ProFinOwner and the ProOwner. This will only succeed if ProOwner and
ProFinOwner are the same. If they are not, you'll get NULL (due to the
left join - with inner join, you'd not have gotten any rows at all).
I'm actually quite surprised that you did see the ProOwner when you
"deleted FinOwner" - but maybe I'm just misunderstanding what you actually
did.
Anyway, to show the name of the two owners, even if they are not the same,
you'll have to join in the client table twice:
SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
e.EmpName AS EmpName,
o.ClientName AS ProOwner,
po.ClientName AS FinOwner
FROM dbo.projects AS p
LEFT OUTER JOIN dbo.employee AS e
ON p.ProPMID = e.EmpUserID
LEFT OUTER JOIN dbo.Clients AS po
ON p.ProOwner = po.ClientId
LEFT OUTER JOIN dbo.Clients AS fo
ON p.ProFinOwner = fo.ClientId
If I were you, I'd also check if you really need all these joins to be
outer joins. Inner joins are usually faster.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo that did it !!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j8ai01lfqn0j5c0l8tig70ne1tu1dqg576@.
4ax.com...
> On Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:
>
> Hi Carlos,
> Your query tries to find ONE row in Clients that is equal to both the
> ProFinOwner and the ProOwner. This will only succeed if ProOwner and
> ProFinOwner are the same. If they are not, you'll get NULL (due to the
> left join - with inner join, you'd not have gotten any rows at all).
> I'm actually quite surprised that you did see the ProOwner when you
> "deleted FinOwner" - but maybe I'm just misunderstanding what you actually
> did.
> Anyway, to show the name of the two owners, even if they are not the same,
> you'll have to join in the client table twice:
> SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
> e.EmpName AS EmpName,
> o.ClientName AS ProOwner,
> po.ClientName AS FinOwner
> FROM dbo.projects AS p
> LEFT OUTER JOIN dbo.employee AS e
> ON p.ProPMID = e.EmpUserID
> LEFT OUTER JOIN dbo.Clients AS po
> ON p.ProOwner = po.ClientId
> LEFT OUTER JOIN dbo.Clients AS fo
> ON p.ProFinOwner = fo.ClientId
> If I were you, I'd also check if you really need all these joins to be
> outer joins. Inner joins are usually faster.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

3 tables join

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.

For eg.

TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}

the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be

id recordType column1

1 TableB value of TableB column1
2 TableC value of TableC column1

Please help.

Cheers
NickOn Nov 8, 3:29 pm, Nick <nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.
>
For eg.
>
TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}
>
the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be
>
id recordType column1
>
1 TableB value of TableB column1
2 TableC value of TableC column1
>
Please help.
>
Cheers
Nick

Hi Nick,

Try:
SELECT a.id, a.recordType, CASE WHEN b.column1 IS NULL THEN c.column1
ELSE b.column1 END AS column1
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.id = a.relatedkey
AND a.recordType = 'TableB'
LEFT OUTER JOIN TableC c
ON c.id = a.relatedkey
AND a.recordType = 'TableC'

Good luck!
J|||On Thu, 08 Nov 2007 07:29:06 -0800, Nick wrote:

Quote:

Originally Posted by

>I have have 3 tables TableA, TableB and TableC. TableA holds the keys
>to TableB and TableC. I need a query which will display the details
>from TableB and TableC depending on the key in TableA.
>
>For eg.
>
>TableA - columns {id, relatedkey, recordType} ===recordType will hold
>values like TableB or TableC
>TableB - columns{id, column1}
>TableC - columns{id, column1}
>
>the query should match the related key to the id of TableA or table B
>based on recordType and show the column1 value with the TabelA id so
>output for this should be
>
>
>id recordType column1
>
>1 TableB value of TableB column1
>2 TableC value of TableC column1
>
>Please help.

Hi Nick,

The solution jhofmeyr posted will work for you. But I think you should
question your design. If TableB and TableC are actually the same thing,
they should be a single table. And if they are different things, then
TableA should have two referencing columns plus a CHECK constraint to
ensure that mey not both be NOT NULL.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

3 table query help

i have 3 tables member_info, subscription_info, exclude
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>

3 table query

I am trying to do something like this but keep getting a syntax error. How would I get something like this?

sql = "SELECT COUNT(optin) AS total_customers_optin FROM (SELECT tbl_customers.*, tbl_register.*, tbl_photos.* FROM tbl_customers, tbl_register, tbl_photos WHERE tbl_register.cust_id = tbl_customers.cust_id AND tbl_photos.photo_id = tbl_register.photo_id AND tbl_photos.photo_date = '04/26/2003' AND tbl_photos.event_id = '109' AND tbl_customers.optin = 'Yes' )">> I am trying to do something like this ...
>> How would I get something like this?

right now you appear to be counting photos

i can think of many queries that are "something like this"

what did you actually want? :)

rudy
http://rudy.ca/|||I am trying to count the number of distinct customers (using distinct email) who have selected Yes in the optin field and registered a photo from the event_id '109' and the photo_date is '04/26/2003'|||try this:SELECT count(DISTINCT tbl_customers.email)
FROM tbl_customers
, tbl_register
, tbl_photos
WHERE tbl_register.cust_id
= tbl_customers.cust_id
AND tbl_photos.photo_id
= tbl_register.photo_id
AND tbl_photos.photo_date = '04/26/2003'
AND tbl_photos.event_id = '109'
AND tbl_customers.optin = 'Yes'rudy|||Thats it!

Thanks for your help.

3 small table database. Query: Return All Suppliers, Offering All Product, Excep

I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.

Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.

Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)

The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.

Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.

SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

(also wrote it using the NOT IN statement)

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)

The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).

Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively

And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)

Our Offers Table contains the following

Supp_ID Prod_ID
111 10
111 13

222 11
222 13
222 14

333 13
333 14
333 15

444 14
444 15

I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.

I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda

I hope I am explaining this well. Any reply is greatly appreciated. Thanks!

(Oh and yes this is just Microsoft SQL Syntax)Try the following:

First create a query of the products you want to show.

SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])

Once this query is created, create the new query using this query instead of Tb_Products|||this is a most interesting problem

let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)

in other words, a given supplier can offer a given product only once

(this is important because we'll be counting rows without using DISTINCT)

the number of products each supplier supplies is given by --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products

first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient

the suppliers we want are those with an "excluded product count" of 0

furthermore, the count of all the products they do supply has to be
3 less than the total number of products

so here is the final query --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
i'm fairly confident in this, but have not tested it

please let me know how it works for you

rudy
http://r937.com/
http://rudy.ca/|||CreativeSoul,

What you are after is called relational division.

Celko has an article that you will find very helpful...

http://www.dbazine.com/celko1.html

Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....

SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))

2-table join where only need one row of second table.

For discussion sake, I've got two tables: customers and orders. What I want
is a query that will return the most recent order for each customer. Let's
say the tables are like:

Customer: customerId, customerName
Orders: orderId, customerId, orderDate

The simple joins I've done give me back all of the orders for all of the
customers.

Any advice?Try

select * from customer c, orders o
where c.customerId = o.customerId
and c.orderId =
(
select max (o2.orderDate)
from orders o2
where o2.customerId = c.customerId
)

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:
> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> > And this is on a dual Xeon with 4 Gig of memory.
> > Here is the script that's taking so long.
> > Would someone be so nice and try it on their SQL Server 2005 and tell me if
> > they have the same issue? Thx.
> >
> > create table #t (tid int, t2 int)
> >
> > select tid, count(*)
> > from #t
> > group by tid with rollup
> >
> > drop table #t
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #t
Speed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me if
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t
|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

25 seconds for query on empty table (SS 2005)

And this is on a dual Xeon with 4 Gig of memory.
Here is the script that's taking so long.
Would someone be so nice and try it on their SQL Server 2005 and tell me if
they have the same issue? Thx.
create table #t (tid int, t2 int)
select tid, count(*)
from #t
group by tid with rollup
drop table #tSpeed of light on both my instances (2005 and 2000 with sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pat" <pat@.online.nospam> wrote in message
news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
> And this is on a dual Xeon with 4 Gig of memory.
> Here is the script that's taking so long.
> Would someone be so nice and try it on their SQL Server 2005 and tell me i
f
> they have the same issue? Thx.
> create table #t (tid int, t2 int)
> select tid, count(*)
> from #t
> group by tid with rollup
> drop table #t|||Thanks for that.
"Tibor Karaszi" wrote:

> Speed of light on both my instances (2005 and 2000 with sp3).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pat" <pat@.online.nospam> wrote in message
> news:ACCC8738-BAAC-4D17-9E6D-98DC9444741B@.microsoft.com...
>

2005 Table Partitioning

Hi - can anyone tell me how I can query which filegroup a particular
partition number belongs to'
For example I have a date range partition function & the query:
SELECT PartitionNo = $partition.DateRangePFN ('20070401')
Tells me this date would be placed in partition number 2 which should be in
a Filegroup called "April" but I just wanted to check that partition 2 is
actually mapped to filegroup April & I can't see how to do it!!
Thanks!!
MikeOn Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> Hi - can anyone tell me how I can query which filegroup a particular
> partition number belongs to'
> For example I have a date range partition function & the query:
> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
> Tells me this date would be placed in partition number 2 which should be in
> a Filegroup called "April" but I just wanted to check that partition 2 is
> actually mapped to filegroup April & I can't see how to do it!!
> Thanks!!
> Mike
select
prv.boundary_id,
prv.value,
fg.name
from
sys.partition_range_values prv
inner join
sys.partition_functions pf on prv.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.destination_id =prv.boundary_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
where
pf.name = 'PF_DateRange'
and
prv.boundary_id = $partition.PF_DateRange('20050814')
See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx|||Exactly what I was after & excellent blog link as well - Thank You!
Mike
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181303483.375856.68170@.r19g2000prf.googlegroups.com...
> On Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
>> Hi - can anyone tell me how I can query which filegroup a particular
>> partition number belongs to'
>> For example I have a date range partition function & the query:
>> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
>> Tells me this date would be placed in partition number 2 which should be
>> in
>> a Filegroup called "April" but I just wanted to check that partition 2 is
>> actually mapped to filegroup April & I can't see how to do it!!
>> Thanks!!
>> Mike
> select
> prv.boundary_id,
> prv.value,
> fg.name
> from
> sys.partition_range_values prv
> inner join
> sys.partition_functions pf on prv.function_id = pf.function_id
> inner join
> sys.destination_data_spaces dds on dds.destination_id => prv.boundary_id
> inner join
> sys.filegroups fg on fg.data_space_id = dds.data_space_id
> where
> pf.name = 'PF_DateRange'
> and
> prv.boundary_id = $partition.PF_DateRange('20050814')
>
> See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx
>

2005 Table Partitioning

Hi - can anyone tell me how I can query which filegroup a particular
partition number belongs to'
For example I have a date range partition function & the query:
SELECT PartitionNo = $partition.DateRangePFN ('20070401')
Tells me this date would be placed in partition number 2 which should be in
a Filegroup called "April" but I just wanted to check that partition 2 is
actually mapped to filegroup April & I can't see how to do it!!
Thanks!!
MikeOn Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> Hi - can anyone tell me how I can query which filegroup a particular
> partition number belongs to'
> For example I have a date range partition function & the query:
> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
> Tells me this date would be placed in partition number 2 which should be i
n
> a Filegroup called "April" but I just wanted to check that partition 2 is
> actually mapped to filegroup April & I can't see how to do it!!
> Thanks!!
> Mike
select
prv.boundary_id,
prv.value,
fg.name
from
sys.partition_range_values prv
inner join
sys.partition_functions pf on prv.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.destination_id =
prv.boundary_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
where
pf.name = 'PF_DateRange'
and
prv.boundary_id = $partition.PF_DateRange('20050814')
See http://weblogs.sqlteam.com/dmauri/a...08/20/7593.aspx|||Exactly what I was after & excellent blog link as well - Thank You!
Mike
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181303483.375856.68170@.r19g2000prf.googlegroups.com...
> On Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> select
> prv.boundary_id,
> prv.value,
> fg.name
> from
> sys.partition_range_values prv
> inner join
> sys.partition_functions pf on prv.function_id = pf.function_id
> inner join
> sys.destination_data_spaces dds on dds.destination_id =
> prv.boundary_id
> inner join
> sys.filegroups fg on fg.data_space_id = dds.data_space_id
> where
> pf.name = 'PF_DateRange'
> and
> prv.boundary_id = $partition.PF_DateRange('20050814')
>
> See http://weblogs.sqlteam.com/dmauri/a...08/20/7593.aspx
>

2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks

2005 Query

I'm having trouble with a SQL Server 2005 XML query.
<Locations>
<Location>
<City>Denver</City>
<State>CO</State>
<ZipCode>80209</ZipCode>
</Location>
<Location>
<City>Oklahoma City</City>
<State>OK</State>
<ZipCode>74804</ZipCode>
</Location>
</Locations>
The query below retuns 1 record: 8020974804 where I want it to return two
records:
80209
74804
How do I configure the query below to do that?
select Convert(nvarchar (100),Locations.query('
data(/Job/Locations/Location/ZipCode)
')) as zipcode
from Locations
You may want nodes() method.
select a.b.query('.')
from
Locations
cross apply
locations.nodes('
/Job/Locations/Location/ZipCode
') a(b)
Pohwan Han. Seoul. Have a nice day.
"Nick K" <nospam@.hotmail.com> wrote in message
news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
> I'm having trouble with a SQL Server 2005 XML query.
> <Locations>
> <Location>
> <City>Denver</City>
> <State>CO</State>
> <ZipCode>80209</ZipCode>
> </Location>
> <Location>
> <City>Oklahoma City</City>
> <State>OK</State>
> <ZipCode>74804</ZipCode>
> </Location>
> </Locations>
> The query below retuns 1 record: 8020974804 where I want it to return two
> records:
> 80209
> 74804
> How do I configure the query below to do that?
> select Convert(nvarchar (100),Locations.query('
> data(/Job/Locations/Location/ZipCode)
> ')) as zipcode
> from Locations
>
|||You probably also want to use
select a.b.value('.', 'int')
instead of the query() method call since you want SQL scalar values and not
XML text nodes.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23C8LRztKGHA.344@.TK2MSFTNGP11.phx.gbl...
> You may want nodes() method.
> select a.b.query('.')
> from
> Locations
> cross apply
> locations.nodes('
> /Job/Locations/Location/ZipCode
> ') a(b)
> --
> Pohwan Han. Seoul. Have a nice day.
> "Nick K" <nospam@.hotmail.com> wrote in message
> news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
>

2005 Query

I'm having trouble with a SQL Server 2005 XML query.
<Locations>
<Location>
<City>Denver</City>
<State>CO</State>
<ZipCode>80209</ZipCode>
</Location>
<Location>
<City>Oklahoma City</City>
<State>OK</State>
<ZipCode>74804</ZipCode>
</Location>
</Locations>
The query below retuns 1 record: 8020974804 where I want it to return two
records:
80209
74804
How do I configure the query below to do that?
select Convert(nvarchar (100),Locations.query('
data(/Job/Locations/Location/ZipCode)
')) as zipcode
from LocationsYou may want nodes() method.
select a.b.query('.')
from
Locations
cross apply
locations.nodes('
/Job/Locations/Location/ZipCode
') a(b)
Pohwan Han. Seoul. Have a nice day.
"Nick K" <nospam@.hotmail.com> wrote in message
news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
> I'm having trouble with a SQL Server 2005 XML query.
> <Locations>
> <Location>
> <City>Denver</City>
> <State>CO</State>
> <ZipCode>80209</ZipCode>
> </Location>
> <Location>
> <City>Oklahoma City</City>
> <State>OK</State>
> <ZipCode>74804</ZipCode>
> </Location>
> </Locations>
> The query below retuns 1 record: 8020974804 where I want it to return two
> records:
> 80209
> 74804
> How do I configure the query below to do that?
> select Convert(nvarchar (100),Locations.query('
> data(/Job/Locations/Location/ZipCode)
> ')) as zipcode
> from Locations
>|||You probably also want to use
select a.b.value('.', 'int')
instead of the query() method call since you want SQL scalar values and not
XML text nodes.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23C8LRztKGHA.344@.TK2MSFTNGP11.phx.gbl...
> You may want nodes() method.
> select a.b.query('.')
> from
> Locations
> cross apply
> locations.nodes('
> /Job/Locations/Location/ZipCode
> ') a(b)
> --
> Pohwan Han. Seoul. Have a nice day.
> "Nick K" <nospam@.hotmail.com> wrote in message
> news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
>

2005 Management Studio Find in Result Grid?

Is there any way to do a find in the query results pane when the results are displayed in a grid? I love using the grid for results because it compacts everything, but I very often have to search through the results.No, not AFAIK.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Unfortunately, we don't offer this functionality. Could you file this as a suggestion on Microsoft Connect? http://connect.microsoft.com/SQLServer/

Post the Connect URL to your bug back in this thread so that others reading this can vote on it.

Lame Workaround:
Can you copy it into Excel?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

2005 Management Studio - New Query

Assume we're using SQL Server authentication. In 2000's Query Analyzer,
once you had logged in, you could open up several new query windows without
having to repeatedly log in each time. Logging in the first time is
important to validate that you are who you say you are. However, in 2005's
Management Studio, every time I open up a new query window I'm prompted for
my credentials. Is there a way to avoid this? I can easily have 10+
windows open at a time.
Thanks in advance.
Mark
Problem solved. The SQL Editor toolbar has an option titled "New Query with
Current Connection" that has the exact behavior I am looking for.
Mark
"Mark" <mark@.nojunkmail.com> wrote in message
news:es9LYcOyFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Assume we're using SQL Server authentication. In 2000's Query Analyzer,
> once you had logged in, you could open up several new query windows
> without having to repeatedly log in each time. Logging in the first time
> is important to validate that you are who you say you are. However, in
> 2005's Management Studio, every time I open up a new query window I'm
> prompted for my credentials. Is there a way to avoid this? I can easily
> have 10+ windows open at a time.
> Thanks in advance.
> Mark
>

2005 Management Studio - New Query

Assume we're using SQL Server authentication. In 2000's Query Analyzer,
once you had logged in, you could open up several new query windows without
having to repeatedly log in each time. Logging in the first time is
important to validate that you are who you say you are. However, in 2005's
Management Studio, every time I open up a new query window I'm prompted for
my credentials. Is there a way to avoid this? I can easily have 10+
windows open at a time.
Thanks in advance.
MarkProblem solved. The SQL Editor toolbar has an option titled "New Query with
Current Connection" that has the exact behavior I am looking for.
Mark
"Mark" <mark@.nojunkmail.com> wrote in message
news:es9LYcOyFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Assume we're using SQL Server authentication. In 2000's Query Analyzer,
> once you had logged in, you could open up several new query windows
> without having to repeatedly log in each time. Logging in the first time
> is important to validate that you are who you say you are. However, in
> 2005's Management Studio, every time I open up a new query window I'm
> prompted for my credentials. Is there a way to avoid this? I can easily
> have 10+ windows open at a time.
> Thanks in advance.
> Mark
>

2005 Management Studio - New Query

Assume we're using SQL Server authentication. In 2000's Query Analyzer,
once you had logged in, you could open up several new query windows without
having to repeatedly log in each time. Logging in the first time is
important to validate that you are who you say you are. However, in 2005's
Management Studio, every time I open up a new query window I'm prompted for
my credentials. Is there a way to avoid this? I can easily have 10+
windows open at a time.
Thanks in advance.
MarkProblem solved. The SQL Editor toolbar has an option titled "New Query with
Current Connection" that has the exact behavior I am looking for.
Mark
"Mark" <mark@.nojunkmail.com> wrote in message
news:es9LYcOyFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Assume we're using SQL Server authentication. In 2000's Query Analyzer,
> once you had logged in, you could open up several new query windows
> without having to repeatedly log in each time. Logging in the first time
> is important to validate that you are who you say you are. However, in
> 2005's Management Studio, every time I open up a new query window I'm
> prompted for my credentials. Is there a way to avoid this? I can easily
> have 10+ windows open at a time.
> Thanks in advance.
> Mark
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
Simon
But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>
|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views ?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created and
> maintains a database. When I login via query analyser I can`t run queries on
> a table unless I qualify it JM.tablename. How do I alter the login to ensure
> i can query just on tablename ?
> Simon
|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon
|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>

2005 Fully qualified names.

In SQL 2005 I have a server with a username of JM. This username created and
maintains a database. When I login via query analyser I can`t run queries on
a table unless I qualify it JM.tablename. How do I alter the login to ensure
i can query just on tablename ?
SimonHi
This is because JM is the owner of the object ,
use sp_changeobjectowner to change the owner form jm to dbo
e.g
sp_changeobjectowner 'Table' , 'dbo'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||But I`m logging onto query analyzer as JM. So I would expect not to have to
qualify the name. If I changed the database ownership to dbo then wouldn`t
that ensure that I`d have to use dbo.tablename ?
Si
"vt" wrote:

> Hi
> This is because JM is the owner of the object ,
> use sp_changeobjectowner to change the owner form jm to dbo
> e.g
> sp_changeobjectowner 'Table' , 'dbo'
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>
>|||Simon
In SQL Server 2005 MS has introduced SCHEMA that all database objects belong
to. Think about a container that holds objects.
You will have to be a memeber of sysadmin server role as well as db_owner
database role.
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||Ok thats cool, I understand this and have changed my ownership accordingly.
Is there an easy way to alter the qualified names in strored procs and views
?
Si
"Simon" wrote:

> In SQL 2005 I have a server with a username of JM. This username created a
nd
> maintains a database. When I login via query analyser I can`t run queries
on
> a table unless I qualify it JM.tablename. How do I alter the login to ensu
re
> i can query just on tablename ?
> Simon|||It is a good idea to always schema qualify all objects anyway.
Andrew J. Kelly SQL MVP
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
> In SQL 2005 I have a server with a username of JM. This username created
> and
> maintains a database. When I login via query analyser I can`t run queries
> on
> a table unless I qualify it JM.tablename. How do I alter the login to
> ensure
> i can query just on tablename ?
> Simon|||I teach my clients that it is MANDATORY to qualify every database object.
:-) There is just no valid reason not to IMHO, and it does save the engine
some effort.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
> It is a good idea to always schema qualify all objects anyway.
> --
> Andrew J. Kelly SQL MVP
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:2CFA854D-47A1-4676-B80D-47341B87D408@.microsoft.com...
>|||I guess I understated that some. I always tell people to qualify objects
no matter what as well.
Andrew J. Kelly SQL MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:eX7ZDnZuHHA.5028@.TK2MSFTNGP02.phx.gbl...
>I teach my clients that it is MANDATORY to qualify every database object.
>:-) There is just no valid reason not to IMHO, and it does save the engine
>some effort.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ujJxPjYuHHA.3480@.TK2MSFTNGP04.phx.gbl...
>