Joining on the same table 2

[Ant0]

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

 

[PHV]

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

 

[AlexCuse]

Something like this perhaps?

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!

 

[AlexCuse]

Or this, if you do have duplicates possible:

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!

 

[Ant0]

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!!!!

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ant0]

PHV, yes it does work. Alex's made me see the light, but yours is correct. Thank you.

 

[PHV]

And isn't mine simpler than your ?

 

[Ant0]

Yours is more succinct, Alex's helped me to remember and understand.