[RESOLVED] query question

[cornercuttinjoe]

ok, its been a while since ive had a question on a query, but im a little stumped.

i have a page that is displaying contacts for customers, so i have 3 tables that are being used

customer_contact(
first_name varchar(16);
last_name varchar(16);
contact_id_num mediumint unsigned not null auto_increment;)

customer(
customer_name varchar(16);
customer_id_num mediumint unsigned not null auto_increment;)

customer_has_customer_contact(
customer_id_num mediumint ....;
contact_id_num mediumint.....;)

thats the gist of it. so here is the deal, i need to get all of the information out of the customer_contact table AND the customer_name for every customer_contact, those who have been related to a customer, and those whose contact_id_num is not in the customer_has_customer_contact;

so basically, i need a query that will return all of the customer_contact info, and if a contact_id_num is present in the customer_has_customer_contact table, i need to the get the customer name for that table. some contacts dont belong to a customer, and some do, but i need to get all of them, and the ones that do have a customer, i need the customer name.

how would i go about this?

the query i was using was

$query = "SELECT DISTINCT first_name, last_name, customer_name FROM
 customer_contact, customer, customer_has_customer_contact WHERE 
customer_contact.contact_id_num = customer_has_customer_contact.contact_id_num 
AND customer.customer_id_num = customer_has_customer_contact.customer_id_num";

any clues?

[cornercuttinjoe]

ok, i have simplified the problem a little bit. i got rid of the customer_has_customer_contact table because it wasnt needed. i then added the customer_id_num field to the customer_contact name.

so they now look like this

customer_contact(
first_name varchar(16);
last_name varchar(16);
customer_id_num mediumint unisigned, 
contact_id_num mediumint unsigned not null auto_increment;)

customer(
customer_name varchar(16);
customer_id_num mediumint unsigned not null auto_increment;)

so this should simplify the query a little bit.

i tried something like

$query = "SELECT DISTINCT first_name, last_name, customer_name FROM 
customer_contact, customer WHERE customer_contact.customer_id_num = 
customer.customer_id_num OR customer_contact.customer_id_num IS NULL";

it doesnt return anything tho. i need it to return everything, and then the customer name if the customer_id_num isnt null.

thanks ahead of time.

[cornercuttinjoe]

answered my own question. if anyone is curious, the solution was

$query = "SELECT first_name, last_name, customer_name FROM customer_contact LEFT JOIN 
customer ON customer_contact.customer_id_num = customer.customer_id_num 
ORDER BY $label";

where $label can be any of the columns.