February 8, 2012 at 3:04 pm
A SELECT statement that returns a name,city and state of each vendor that is located in a unique city and state. In other words, don't include vendors that have a city and state that is common with another vendor.
February 8, 2012 at 3:05 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 3:11 pm
As Gail said, please give us more info. Anything provided without that info us just a guess.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2012 at 3:33 pm
Hi...seems that from your previous posts that you are currently a student.
...no probs with that, but you will learn a lot faster if you at least provide what you you have tried so far.
as requested before ...learn to provide the required table definitions, sample data and desired output...if this is unclear then ask for help.
to answer your question....
heres an idea to start you off
SELECT city, COUNT(*) AS SingleVendor_byCity
FROM YOURDATA_TABLE
GROUP BY city
HAVING (COUNT(*) = 1)
...i am assuming that a city can only be associated with one state
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 9, 2012 at 6:20 am
Yes I am a new student to TSQL and SS2K8. its a bit different than DB2s SQL. A lot of new code to learn. Last night at class I had the instructor explain it to me and the class. The solution is this:
SELECT VendorName
,VendorCity
,VendorState
FROM Vendors
WHERE VendorCity NOT IN
(SELECT v1.VendorCity
FROM Vendors AS v1
JOIN Vendors AS v2
ON (v1.VendorID <> v2.VendorID) AND
(v1.VendorCity = v2.VendorCity AND
v1.VendorState = v2.VendorState));
February 9, 2012 at 7:06 am
Ask your "instructor" what will happen if two different states have the city with the same name?
The solution given to you is not only wrong, but also would perform badly as well.
One of the way to achieve it would be:
SELECT v1.VendorName, v1.VendorCity, v1.VendorState
FROM Vendor AS v1
LEFT JOIN Vendor AS v2
ON v2.VendorCity = v1.VendorCity
AND v2.VendorState = v1.VendorState
AND v2.VendorId != v1.VendorId
WHERE v2.VendorId IS NULL
OR
SELECT v1.VendorName, v1.VendorCity, v1.VendorState
FROM Vendor AS v1
WHERE NOT EXISTS( SELECT 1
FROM Vendor AS v2
WHERE v2.VendorCity = v1.VendorCity
AND v2.VendorState = v1.VendorState
AND v2.VendorId != v1.VendorId )
February 9, 2012 at 7:15 am
J Livingston SQL (2/8/2012)
Hi...seems that from your previous posts that you are currently a student....no probs with that, but you will learn a lot faster if you at least provide what you you have tried so far.
as requested before ...learn to provide the required table definitions, sample data and desired output...if this is unclear then ask for help.
to answer your question....
heres an idea to start you off
SELECT city, COUNT(*) AS SingleVendor_byCity
FROM YOURDATA_TABLE
GROUP BY city
HAVING (COUNT(*) = 1)
...i am assuming that a city can only be associated with one state
I believe the COUNT(*) needs to be COUNT(DISTINCT VendorName) to allow a single vendor to have more than one presence in the city.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2012 at 7:23 am
Eugene and wurkenmomz ,
The <> in both of your code creates a "double Triangular Join". If you use that code (making the appropriate substitutions for the different table) on the AdventureWorks.Person.Address table, it creates more than 2 million internal rows to filter through just 19 thousand and takes more than 6 seconds to run on my 10 year old box.
Please see the following article for what "Triangular Joins" are and how they can devestate a server by generating internal rows.
http://www.sqlservercentral.com/articles/T-SQL/61539/
I could be wrong because the OP didn't provide any test data nor expected results for that data, but I believe the following is what is needed and it doesn't have the "Triangular Join" problem.
SELECT addr1.VendorName, addr1.VendorCity, addr1.VendorState
FROM dbo.Vendors addr1
INNER JOIN
(
SELECT VendorCity, VendorState
FROM dbo.Vendors
GROUP BY VendorCity, VendorState
HAVING COUNT(DISTINCT VendorName) = 1
) unqaddr
ON addr1.VendorCity = unqaddr.VendorCity
AND addr1.VendorState = unqaddr.VendorState
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2012 at 7:36 am
Jeff, that why I said "one of". I was afraid to recommend using "HAVING COUNT..." as it could be even harder to explain to his "instructor".
However, if you look into instructor provided solution, you can see that Vendor table has VendorId, therfore I wouldn't recommend "distincting" by name, as you may have two different John Smith's easely and happily leaving in the same city, therefore records for this city shouldn't be returned as per requirements. You are absolutely right about trangular joins. But, I still think that the query should not check for distinct names, instead HAVING COUNT(*) = 1 should be used.
February 9, 2012 at 7:45 am
I believe the COUNT(*) needs to be COUNT(DISTINCT VendorName) to allow a single vendor to have more than one presence in the city.
absolutely agree...but should it be VendorID (not vendorName) ??
.....(.but in defence I did say "an idea to start you off" and in the absence of any data ???.....:-) )
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 9, 2012 at 8:02 am
J Livingston SQL (2/9/2012)
I believe the COUNT(*) needs to be COUNT(DISTINCT VendorName) to allow a single vendor to have more than one presence in the city.
absolutely agree...but should it be VendorID (not vendorName) ??
.....(.but in defence I did say "an idea to start you off" and in the absence of any data ???.....:-) )
If the VendorID is ID in the Vendor table, it is right to assume it is unique. Therefore, there is no need to do COUNT(DISTINCT...)
COUNT(*) will be just fine and right.
February 10, 2012 at 4:07 pm
Eugene Elutin (2/9/2012)
J Livingston SQL (2/9/2012)
I believe the COUNT(*) needs to be COUNT(DISTINCT VendorName) to allow a single vendor to have more than one presence in the city.
absolutely agree...but should it be VendorID (not vendorName) ??
.....(.but in defence I did say "an idea to start you off" and in the absence of any data ???.....:-) )
If the VendorID is ID in the Vendor table, it is right to assume it is unique. Therefore, there is no need to do COUNT(DISTINCT...)
COUNT(*) will be just fine and right.
"It Depends". If there are two of the same franchise (with the same VendorID) in the same city, the COUNT(*) won't work correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2012 at 4:45 pm
Thank you everyone for your help the answers, my instructor helped me figure this one out. The answer he was looking for was this:
SELECT VendorName
,VendorCity
,VendorState
FROM Vendors
WHERE VendorCity NOT IN
(SELECT v1.VendorCity
FROM Vendors AS v1
JOIN Vendors AS v2
ON (v1.VendorID <> v2.VendorID) AND
(v1.VendorCity = v2.VendorCity AND
v1.VendorState = v2.VendorState));
It works!!! Great!!!
February 10, 2012 at 4:47 pm
Also, he states that using the Distinct will not get the recommended results and can be very unreliable, in these cases. He does this for a living.
February 10, 2012 at 5:36 pm
wurkenmomz (2/10/2012)
Also, he states that using the Distinct will not get the recommended results and can be very unreliable, in these cases. He does this for a living.
Perfect. The instructor must be correct especially since he does this for a living. The rest of us all do this in our spare time just for fun and only every other weekend and only if it's not nice outside. 😛
Just remember that your instructor is the one that wrote the "Triangular Join" and then lookup "DISTINCT" in Books Online and see if it's really as "unreliable" as he says. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply