joining one table multiple times to other tables

Question

I have three tables:

Table User( userid username)

Table Key( userid keyid)

Table Laptop( userid laptopid)

i want all users who have either a key or a laptop, or both. How do i write the query so that it uses a join between table User and table Key, as well as a join between table User and table Laptop?

The main problem is that in the actual scenario, there are twelve or so table joins, sth like:

" select .. From a left join b on (...), c join d on (..),e,f,g where ...",

and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?

Answer 1

You can use multiple joins to combine multiple tables:

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid

A "left join" also finds users which do not have a key or a laptop. If you replace both with "inner join", it would find only users with a laptop and a key.

When a "left join" does not find a row, it will return NULL in its fields. So you can select all users that have either a laptop or a key like this:

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid
where k.userid is not null or l.userid is not null

NULL is special, in that you compare it like "field is not null" instead of "field <> null".

Added after your comment: say you have a table Mouse, that is related to Laptop, but not to User. You can join that like:

select *
from user u
left join laptop l on l.userid = u.userid
left join mouse m on m.laptopid = l.laptopid

If this does not answer your question, you gotta clarify it some more.

Answer 2

select distinct u.userid, u.username
from User u 
    left outer join Key     /* k on u.userid = k.userid */
    left outer join Laptop  /* l on u.userid = l.userid */
where k.userid is not null or l.userid is not null

EDIT "The main problem is that in the actual scenario, there are twelve or so table joins, sth like: " select .. From a left join b on (...), c join d on (..),e,f,g where ...", and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?"

You can have as many left outer joins as required. Join the table with the primary key to the rest of the tables or on any other field where field values of one table should match field values of other table.

eg will explain better than words

select * 
from a
 left outer join b on a.pk = b.fk -- a pk should match b fk
 left outer join c on a.pk = c.fk -- a pk should match c fk
 left outer join d on c.pk = d.fk -- c pk should match d fk

and so on