relational select query 1

[zimma]

Hello,

I want to run a query like the following...

SELECT UNIX_TIMESTAMP(l.DATE), l.ACTION l.EVENT_ID, l.OPERATOR_ID, l.ORGANISER_ID
FROM log l
ORDER BY l.DATE DESC
LIMIT 200

but I would also like to relate the operator, organiser and event ids to their own tables so I can present the name of the item in the result rather than the ID. The only problem is that often there may not be a related organiser or event in which case they will be set to null and so be left out in a relational query which I really don't want to happen.

Any ideas?

 

[r937]

use a LEFT OUTER JOIN

r937.com | rudy.ca

 

[Kozusnik]

r937 is correct (per usual), by using the LEFT OUTER JOIN, the value from the primary table will not be lost if a corresponding value is not found in the "lookup" table.

SELECT UNIX_TIMESTAMP(l.DATE), l.ACTION l.EVENT_ID, l.OPERATOR_ID, l.ORGANISER_ID, organiser_tbl.description, operator_tbl.description
FROM log l
LEFT OUTER JOIN organiser_tbl on organiser_tbl.key=l.ORGANISER_ID
LEFT OUTER JOIN operator_tbl on operator_tbl.key= l.OPERATOR_ID
ORDER BY l.DATE DESC
LIMIT 200

This example shows that you can use multiple joins in the same command.

Mark

 

[Kozusnik]

I should mention to be sure that you have an index (or primary key) on every key field you use for joining (the ID fields). It will make your query run significantly faster when you start pulling large amounts of data.

Mark