Help with SELECT statement

[eluser]Samuurai[/eluser]
Hi,

I've only ever done simple SELECT statements, but am trying to advance to the world of JOINS and selecting from multiple tables.

I'm writing an app to track server downtimes for my organisation.

I have 5 tables
One called application which has an id and Name field
One called device which has an id and Name field
One table called "downtime", which has an "id" some fields with data and also application_id and device_id
I also have two lookup tables named downtime_application and downtime_device.

I'm ok with the basic SELECT * FROM downtime, but how do I then use the application_id and device_id do pull the application and device name ?

[eluser]bgreene[/eluser]
select t.*,a.name as appname,d.name as devname from downtime t
left join application a on a.id = t.application_id
left join device d on d.id = t.device_id

use "left join" rather than "join" because in the case of eg an invalid
device_id in the downtime table ie the value is not in the device table,
then the downtime event will still show up in your query result but with
null as devname

[eluser]Samuurai[/eluser]
Wow that worked brilliantly!

There's a lot of short-hand in there though... why do you select t.* Does create some sort of alias?
The same for a.name?

[eluser]jedd[/eluser]
[quote author="Samuurai" date="1248727669"]
There's a lot of short-hand in there though... why do you select t.* Does create some sort of alias?[/quote]

For readability, particularly until you get the hang of things you might want to avoid those shorthands. Even now I tend to avoid them unless I'm writing an especially wordy query.

Consider this segment of bgreene's statement:

select t.*,a.name as appname,d.name as devname from downtime t

The 'downtime t' at the end is where it's aliased, and there's a good case to be made for consistently using AS (even though it's technically optional, at least with MySQL). So I'd write downtime AS t there. I also upper-case all my reserved words, which I think further aids readability.

[eluser]Samuurai[/eluser]
Thanks a lot everyone !

This is how my code turned out after I dumbed it down

Code:

$this->db->select('downtime.*, application.name AS appname, device.name AS devname');
$this->db->join('application', 'application.id = downtime.application_id', 'left');
$this->db->join('device', 'device.id = downtime.device_id', 'left');
$this->db->where('status = 1');
$query = $this->db->get('downtime');

[eluser]Samuurai[/eluser]
I just noticed... where in this select statement does it reference the lookup tables, downtime_device and downtime_application?

[eluser]bgreene[/eluser]
it doesen't 8=) and probably doesn't need to. the data should all be in the downtime table. to check the downtime for a specific device just do a "select x,y,z, sum(timedown) from etc where device_id = 'the deviceid to check' group by device_id".
the "sum" function requires "group by". you can also omit the "where" and get a listing for all devices but then add eg "order by devicename" or whatever so it looks nice

[eluser]Samuurai[/eluser]
Oh yeah, all the data is in the outage table..

I figured because an application can have many downtimes and a downtime can have many applications, it would be a MANY TO MANY relationship, which usually needs a lookup table.

In what instance is a lookup table required then?

[eluser]bgreene[/eluser]
the advantage of a lookup table is eg you can change a device name in the device table and all reports will immediately show the new name without you having to rename countless occurrences in the downtime table. it also makes your code less error prone as you are comparing only integers rather than case- and spelling- sensitive strings. in an accounting system for example, a customer shows up in very many places but his name will only appear in one table and all other references will be via his id. you only need to change his name once. the same data should never ever be entered more than once in a system

[eluser]Samuurai[/eluser]
Right, yeah that makes sense... So in my case, I probably don't want to have a lookup table for this because if a device is renamed or re-tasked, the details of the outage will no longer make sense.

Is a select using a lookup table still done using a join or is it multiple select statements?

(thanks very much for your comments btw)