CodeIgniter Forums
MySQL join same table twice? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL join same table twice? (/showthread.php?tid=30358)

MySQL join same table twice? - El Forum - 05-12-2010

[eluser]seanloving[/eluser]
I have a 'contacts' table with these fields:
Code:
id | name | email_id | phone_id | fax_id
---|------|----------|----------|-------
3  | sean |     8    |    5     |   16

I have an 'emails' table with these fields:
Code:
id | type | email
---|------|---------------
8  | work | [email protected]

I have a 'numbers' table with these fields:
Code:
id | type  | number
---|-------|--------------
5  | phone | 303-555-1212
16 | fax   | 303-555-9999
I want a query whose result will look like this:

username = sean
emailaddr = [email protected]
telephone = 303-555-1212
facsimile = 303-555-9999

I'm struggling because phone_id and fax_id both identify unique records in the numbers table. Do I join the same table twice? I'm wondering how to properly write this query. Here's what does not work:

Code:
return $this->CI->db
            ->select( " name AS username,
                        email AS emailaddr,
                        numbers.number AS telephone,
                        numbers.number AS facsimile,
                        ",
                        FALSE)
            ->from('contacts')
            ->join('emails', 'contacts.id = emails.id', 'left')
            ->join('numbers', 'numbers.id = contacts.phone_id', 'left')
            ->join('numbers', 'numbers.id = contacts.fax_id', 'left')
            //->where('numbers.id', 'contacts.phone_id')
            ->get()->result_array();

What active record method should I be using? where_in()? or_where()? or_where_in()???

Thanks
Sean Loving

MySQL join same table twice? - El Forum - 05-12-2010

[eluser]Buso[/eluser]
If an email address belongs only to one 'contact', why make a separate table?

Anyway you could check this http://www.overzealous.com/dmz/ and forget about SQL =P

MySQL join same table twice? - El Forum - 05-12-2010

[eluser]LuckyFella73[/eluser]
Sorry that I can't help you with the query because
I allways have to test alot before getting my join-querys
to run.
I think it would be easier if you set up a column in the
"email" and "numbers" tables called "id_user" or something like
that and insert the id from "contacts". If you handle it like
you have it now you will get more stress when you start to
add more lines in the "numbers" table related to an existing
user. Maybe a user can have more than one email address or
phone number. With the actual structure you will end up with
many id's in you user-table.

Simular to that:
Code:
id | name |
---|------|
3  | sean |



id | id_user | type  | email
---|---------|---------------
8  |     3   |  work | [email protected]



id | id_user | type   | number
---|---------|-----------------------
5  |    3    | phone  | 303-555-1212
6  |    3    | mobile | whatever number
16 |    3    | fax    | 303-555-9999

It's a bit more flexible I think

MySQL join same table twice? - El Forum - 05-12-2010

[eluser]Buso[/eluser]
I recommend you to stick to the naming convention of 'user_id' instead of 'id_user'. Some day you may want to start using an ORM or another framework.

MySQL join same table twice? - El Forum - 05-12-2010

[eluser]seanloving[/eluser]
[quote author="LuckyFella73" date="1273690263"]
It's a bit more flexible I think[/quote]

I see what you mean, thanks.

MySQL join same table twice? - El Forum - 05-13-2010

[eluser]seanloving[/eluser]
[quote author="LuckyFella73" date="1273690263"]

Code:
id | name |
---|------|
3  | sean |



id | id_user | type  | email
---|---------|---------------
8  |     3   |  work | [email protected]



id | id_user | type   | number
---|---------|-----------------------
5  |    3    | phone  | 303-555-1212
6  |    3    | mobile | whatever number
16 |    3    | fax    | 303-555-9999

It's a bit more flexible I think[/quote]

Guys,
given the suggested data model above, I'm still unclear on how to write a single query that will provide these results:

Code:
name | phone         | fax
-----|---------------|-------------
sean | 303-555-1212  | 303-555-9999

Any help much appreciated.

SL

MySQL join same table twice? - El Forum - 05-14-2010

[eluser]Aken[/eluser]
LuckyFella73's example is probably a better way to go, if you want a more dynamic variation of what numbers and emails are attached to certain contacts. And you only have to update one table if you're adding or modifying a number.

Using his example, your query would be something like:

Code:
$this->db->select('c.user_id, name, p.number AS phone, f.number AS fax', false)
        ->from('contacts c')
        ->where('c.user_id', 3, false)
        ->join('numbers p', "p.user_id = c.user_id AND p.type = 'phone'", 'left')
        ->join('numbers f', "f.user_id = c.user_id AND f.type = 'fax'", 'left');
        
$q = $this->db->get();

That is untested, so apologies if there are any small mistakes in there.

But basically, you join the same table twice, but renaming it to something else, turning it into its own entity. Then you add the phone "type" in there to force it to only pick up that certain row, since the left join without it would grab all three phone types.

You'll notice I didn't use "AS" in some places, also. You technically don't have to, and it's how I usually code. Just a tidbit.

Let us know how that works out, or if you have any other questions.

MySQL join same table twice? - El Forum - 05-14-2010

[eluser]seanloving[/eluser]
[quote author="Aken" date="1273836133"]
...
But basically, you join the same table twice, but renaming it to something else, turning it into its own entity. Then you add the phone "type" in there to force it to only pick up that certain row, since the left join without it would grab all three phone types.
...
Let us know how that works out, or if you have any other questions.[/quote]

Aken, thanks for the MySQL 101. It works now.