[thelist] [SQL] selecting info from two tables (?)

[Paul Cowan]

Paul Bennett wrote:
> I am querying the "events" table, which gives me (among other things) a
> userId. I want to then get the organisationID of this user (if any) from
> the "users" table and get the organisation name corresponding to this id
> from the "organisations" table in ONE query. Am I dreaing, or is this
> possible

>From your use of 'if any', I'll assume that there are no guarantees in
this -- an event may not have a user, and a user may not have an
organisation.

Try this:

SELECT
	events.id, some_other.fields, organisations.org_name
FROM
	events
LEFT JOIN users ON
	(events.userID = users.userID)
LEFT JOIN organisations on
	(users.orgId = organisations.id)

Taa-daa! That should do the trick.

If an event ALWAYS has an associated user, the first LEFT JOIN can
be an INNER JOIN; if a user ALWAYS has an associated organisation also,
then the second one can be too (but only if the first one is, unless
you rearrange the query)

Cheers,

Paul