sql query help

Steve Hougom
Steve Hougom used Ask the Experts™
on
I need help with a sql query to select all columns where 2 state codes(PQ and QC) share the same city code.  I dont know the value of CityCode.  

All of these columns are in a table called tbCompanyCityMaster.

psuedo code below.

select * from
[tbCompanyCityMaster] 
where state in('QC','PQ') and CityCode (is same citycode for both 'QC' and 'PQ')  

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I assumed that you have an ID column which is a unique value for each rows.
select * from
[tbCompanyCityMaster] AS t1, [tbCompanyCityMaster] AS t2, 
where t1.ID<>t2.ID AND t1.state in('QC','PQ') and t2.state in('QC','PQ') and t1.CityCode=t2.CityCode   

Open in new window

Steve HougomDeveloper II

Author

Commented:
thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial