SQL build search query to match multiple ids

Question

I've become stuck on particular problem of finding common items between tables. I have to works with these 3 tables - People can own have more than one home.

I want to find all people that have owned the same home as "John Doe"

Persons
    +------+------------------+----------+
    | id   | firsname         | lastname |
    +------+------------------+----------+
    |    1 | John             | Doe      |
    +------+------------------+----------+

Home
+------+------------------+-----------+
| h_id |     address      | year_built|
+------+------------------+-----------+
|    1 | 1233 SQL PL NW   | 1995      | 
+------+------------------+-----------+

Ownership
+-----------+------------------+
| person_id |     house_id     |
+-----------+------------------+
|    1      |         1        |
+-----------+------------------+

My current thoughts on what the query should go is that I check Persons.id matches the ownership.id then find the house.id and compare it two a some Person2.

Conrad Frix · Accepted Answer · 2011-05-27T05:28:15.733

4

Basically you need to go

persons -> 
    ownership -> 
       ownership that's not John Doe -> 
         back to persons again

This should do it

SELECT p2.id,
       p2.first_name,
       p2.last_name
FROM   persons p 
   INNER JOIN ownership o 
     ON p.id = o.person_id 
   INNER JOIN ownership o2 
     ON p.id <> o2.person_id 
        AND o.house_id = o2.house_id 
   INNER JOIN persons p2 
     ON o2.person_id = p2.id 
WHERE p.id = 1

as dtbarne notes the where clause assume you know the ID. If you want you change the WHERE clause to

WHERE p.first_name = 'John' and p.Last_name = 'Doe'

The problem there of course is if two records in person share the name you'll get multiple results so you want to add House information to the SELECT clause to differentiate

edited May 27 '11 at 05:28

answered May 27 '11 at 05:06

Conrad Frix

51,984
12
96
155

Assumes you know the ID of John Doe. – dtbarne May 27 '11 at 05:10
You don't know the name of John Doe, but you have the id of p2. Also, thank you very much for you response. – Chris May 27 '11 at 05:22

score 0 · Answer 2 · answered May 27 '11 at 05:07

Try this: You want to find all of the people who own a house where the house Id is in the list of the subquery, which is all house(s) owned by John Doe.

Note: column name firsname (sic) matches your question, may be a typo.

select po.*
from Persons po
inner join ownership oo
on po.id = oo.person_id
where house_id in (select house_id 
  from ownership o 
  inner join persons p
  on p.id = o.person_id
  where p.firsname = 'John' and p.lastname = 'Doe'
)

dtbarne · Answer 3 · 2011-05-27T05:20:40.017

0

SELECT Persons.*
FROM Persons, Ownership
WHERE Persons.id = Ownership.person_id
    AND Ownership.house_id = 
        (SELECT Home.h_id
         FROM Home, Persons
         WHERE Home.h_id = Ownership.house_id
            AND Ownership.person_id = id
            AND Persons.firsname = 'John'
            AND Persons.lastname = 'Doe' LIMIT 1);

edited May 27 '11 at 05:20

answered May 27 '11 at 05:08

dtbarne

8,110
5
43
49

Is it considered more stylistically correct to use JOINs?(Honest question, still learning) – Chris May 27 '11 at 05:15
Fair question, probably worth a post itself. I find it easier to read and work with the syntax I posted, although I get the sense I'm in the minority. :) – dtbarne May 27 '11 at 05:20
Yes ANSI 92 style joins are perferred. At the very least you see when you're missing a join and don't end up with a Cartesian product. For example Home isn't joined to anything – Conrad Frix May 27 '11 at 05:21
By the way, the syntax I used is still an INNER JOIN. It's just a different way to write it. – dtbarne May 27 '11 at 05:21
@Conrad Frix Ummm...that post proves that they are indeed identical. EDIT: I see you removed that comment. – dtbarne May 27 '11 at 05:28
@dtbarne You noticed :) I was looking for [this post](http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338) which included the bit about `*<` vs `Left Join` and grabbed the wrong post. Ended up deleting it because and I couldn't edit it in time. – Conrad Frix May 27 '11 at 05:42
query is incorrect. the limit 1 will make it return only one house (and not necessarily the one Joe currently owns). – Denis de Bernardy May 27 '11 at 08:15

score 0 · Answer 4 · edited May 27 '11 at 08:10

0

SELECT p.*
FROM persons p 
   INNER JOIN ownership o 
     ON p.id = o.person_id  
     INNER JOIN Home h 
     ON o.house_id = h.h_id 
WHERE p.firsname = 'John' and p.lastname = 'Doe'

edited May 27 '11 at 08:10

Andriy M

76,112
17
94
154

answered May 27 '11 at 05:15

SQL build search query to match multiple ids

4 Answers4