views:

128

answers:

2

So I've asked a couple of questions about performing joins and have had great answers, but there's still something I'm completely stumped by.

I have 3 tables. Let us call them table-b, table-d and table-e.
Table-b and table-d share a column called p-id.
Table-e and table-b share a column called ev-id.
Table-e also has a column called date.
Table-b also has a unique id column called u-id.

I'd like to write a query which returns u-id under the following conditions:
1) Restriced to a certain value in table-e.date.
2) Where table-b.p-id does not match table-d.p-id.

I think I need to inner join table-b and and table-e on the e-id column. I then think I need to perform a left join on table-d and and table-b where p-id is null.
My problem is that I don't know the syntax of writing this query. I know how to write multiple inner joins and I know how to write a left join. How do I combine the two?

Thanks so much to everyone who is helping me out. I'm (obviously!) a newbie to databases and am struggling to get my head around it all!

+3  A: 

You just write the joins one after the other:

SELECT b.uid
  FROM b
  INNER JOIN e USING(evid)
  LEFT JOIN d USING(pid)
  WHERE e.date = :whatever
    AND d.pid IS NULL
Alex Martelli
A: 

I think it's something like this:

SELECT uid
  FROM table-b
  INNER JOIN table-e
      ON table-b.ev_id = table-e.ev_id
  WHERE table-b.p_id NOT IN (SELECT p_id from table-d)
Jeremy Smyth
1) You forgot the table-e.date=XXX condition 2) I find this clearer than the one from @Alex however I believe that mysql would run this slower if table-d has many rows (even though the subselect will be run only once)
daremon