Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining on the same table 2

Status
Not open for further replies.

Ant0

Technical User
May 3, 2005
17
GB
Hi there

Been away from SQL for about a year and I am really struggling on something I think is rather basic. I have a table, PARTS, it contains the following two columns Part_No and City, I want to return all Part_No where the city for that Part_No is London and Frankfurt. See sample below:

Part_No | City
------------
3 | Stockholm
4 | London
4 | Frankfurt
5 | London
5 | Stockholm
6 | Milan

So the query sould return Part_No 4 because it has cities London and Frankfurt. Please help me, otherwise I think I could rip out all of my hair.

Cheers
 
A starting point:
SELECT L.Part_No
FROM PARTS L INNER JOIN PARTS F ON L.Part_No = F.Part_No
WHERE L.City = 'London' AND F.City = 'Frankfurt'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Something like this perhaps?

Code:
select PART_NO
from leTable a
where CITY = 'London' or CITY = 'Frankfurt'
group by PART_NO
having count(*) > 1

With that particular where clause, your part_no has to appear in both cities (or in one city twice, which you would never allow to happen right?) to get the required count to be displayed.

Hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!
 
Or this, if you do have duplicates possible:

Code:
select PART_NO
from (
	select distinct PART_NO, CITY from leTable where CITY = 'London'
	union all
	select distinct PART_NO, CITY from leTable where CITY = 'Frankfurt'
) a
where CITY = 'London' or CITY = 'Frankfurt'
group by PART_NO
having count(*) > 1

Hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!
 
Hi Alex

Your answers helped a lot! This is what I am using:

SELECT a.part_no
FROM (SELECT DISTINCT part_no, city
FROM leTable
WHERE city = 'LONDON') a,
(SELECT DISTINCT part_no, city
FROM leTable
WHERE city = 'FRANKFURT') b
WHERE a.part_no = b.part_no

Perfect!!!!
 
Ant0, just to know, didn't my suggestion works for you ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV, yes it does work. Alex's made me see the light, but yours is correct. Thank you.
 
Yours is more succinct, Alex's helped me to remember and understand.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top