SQL Statement Question

Question

Post reply

SQL Statement Question

libpcap

Grasshopper

Points: 18
More actions
April 21, 2004 at 6:58 pm

#161219

I have a SQL question thought anyone can help...
MyTable:
Company City
SBC LA
SBC NY
Deluxe LA
Vision LA
Vision NY
Vision Chicago
I want to get companies that have presence in both LA and in NY. Deluxe only has presence in LA and not in NY, so it shouldn't be returned in the query result. Does anyone know how to accomplish this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Hi,

Assuming your table name to temp this would work

SELECT COMPANY FROM TEMP WHERE COMPANY IN (SELECT COMPANY FROM TEMP WHERE CITY='LA') AND CITY='NY'

Prasad Bhogadi
www.inforaise.com

AH SSC Veteran Points: 267 More actions · Answer 2

The query below would give better performance....

SELECT Company

FROM TEMP

WHERE City IN ('LA','NY')

GROUP BY Company

HAVING COUNT(*) = 2

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Yeah, this is a better alternative.

Thanks

Prasad Bhogadi
www.inforaise.com

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

Well, there is one issue though which may not be the case like if say a company name is repeated for the city then it would also appear when we group with having clause. If the combination is unique which may be the case, it works better than my query without a doubt.

Thanks

Prasad Bhogadi
www.inforaise.com

libpcap Grasshopper Points: 18 More actions · Answer 5

MyTable:

Company	City
SBC	LA
SBC	NY
Deluxe	LA
Vision	LA
Vision	NY
Vision	Chicago

Let me revise the question: I only want companies that have presence in all cities where SBC has presence. SBC is located in Los Angeles and New York, so I want to get only companies that are in both New York and Los Angeles. I think Newbie's answer will work or can be modified easily to accomodate this.

SELECT Company

FROM Temp

WHERE City IN ( SELECT City FROM Temp c1 WHERE c1.Company = 'SBC')

GROUP BY Company

HAVING count(company) = ( SELECT count(*) FROM Temp c2 WHERE c2.company = 'SBC')

Thanks Newbie.