[RESOLVED] MySql Join - Is this possible in Mysql?

[KenBZim2]

I have 2 tables:

`contact_domain`

contact_domain_id (INT) [Primary Key]
display_name VARCHAR(30)



`contact_lines`
contact_line_id (INT) [Primary Key]
contact_domain_id (INT) [FK]
contact_entry_type_id (INT)
actual_data VARCHAR(30)



When i run the following query:

select
`contacts_domain`.`display_name` AS `display_name`,
if((`contacts_lines`.`contact_entry_type_id` = 1),`contacts_lines`.`actual_data`,_latin1'') AS `Landline`,
if((`contacts_lines`.`contact_entry_type_id` = 2),`contacts_lines`.`actual_data`,_latin1'') AS `Mobile`,
if((`contacts_lines`.`contact_entry_type_id` = 3),`contacts_lines`.`actual_data`,_latin1'') AS `Fax`,
if((`contacts_lines`.`contact_entry_type_id` = 4),`contacts_lines`.`actual_data`,_latin1'') AS `Email`,
if((`contacts_lines`.`contact_entry_type_id` = 5),`contacts_lines`.`actual_data`,_latin1'') AS `Website`
from
(`contacts_domain` join `contacts_lines` on((`contacts_domain`.`contact_domain_id` = `contacts_lines`.`contact_domain_id`)))

The record set returns:

display_name	Landline	Mobile		Fax		Email			Website
Joe Soap 	555214544
Joe Soap 			7814544
Joe Soap 					4544444
Joe Soap 							joe@somewhere
Joe Soap 										www.somewhere.com

What I am looking for is:

display_name	Landline	Mobile		Fax		Email			Website
Joe Soap 	555214544	7814544		4544444		joe@somewhere		www.somewhere.com

Is this possible in Mysql?

Any help would be appreciated

[si_the_geek]

Join to the second table multiple times (with aliases), once for each type of info you want to grab, using appropriate Where clauses, eg:

select
`contacts_domain`.`display_name` AS `display_name`,
`Landline`.`actual_data` AS `Landline`,
`Mobile`.`actual_data` AS `Mobile`,
...
from
`contacts_domain` 
LEFT join `contacts_lines` as `Landline` on (`contacts_domain`.`contact_domain_id` = `contacts_lines`.`contact_domain_id` AND `contacts_lines`.`contact_entry_type_id` = 1)
LEFT join `contacts_lines` as `Mobile` on (`contacts_domain`.`contact_domain_id` = `contacts_lines`.`contact_domain_id` AND `contacts_lines`.`contact_entry_type_id` = 2)
...

[KenBZim2]

Thanks, I tried this:

select
`contacts_domain`.`display_name` AS `display_name`,
`Landline`.`actual_data` AS `Landline`,
`Mobile`.`actual_data` AS `Mobile`
from
`contacts_domain`
LEFT join `contacts_lines` as `Landline` on (`contacts_domain`.`contact_domain_id` = `contacts_lines`.`contact_domain_id` AND `contacts_lines`.`contact_entry_type_id` = 1)
LEFT join `contacts_lines` as `Mobile` on (`contacts_domain`.`contact_domain_id` = `contacts_lines`.`contact_domain_id` AND `contacts_lines`.`contact_entry_type_id` = 2)

But i get the following error:

Unknown Column 'contacts_lines.contact_domain_id' in 'On Clause'

Even though the column exists in that table??

[si_the_geek]

Ah yes, I missed that part... in the On clauses you need to use the aliases rather than the original table name, eg:

LEFT join `contacts_lines` as `Landline` on (`contacts_domain`.`contact_domain_id` = `Landline`.`contact_domain_id` AND `Landline`.`contact_entry_type_id` = 1)

[KenBZim2]

Thank you si_the_geek, works perfectly