struggling with join

[wmguk]

Hey,

 

I have 3 tables

 

clients                client_id| Company name | address

contacts          contact_id| client_id | contact name

contact_coms    coms_id    | contact_id | content

 

$sql = "SELECT clients.company, clients.client_id, contacts.client_id, contacts.name, contacts.contact_id, contact_coms.contact_id, contact_coms.com_content ".
"FROM clients, contacts, contact_coms ".
"WHERE (clients.client_id = contacts.client_id) ".
"AND (contacts.contact_id = contact_coms.contact_id) ";

 

the problem is that I only get the results that have something in contact_coms.... I need to show all records including where there is no result for the coms....

 

what am i doing wrong?

[kittrellbj]

The last line of your SQL query is requiring there to be something in that table for it to return the record.  "AND ..." means it only returns records where both are true.  Since some records don't have that, it will not return them.

 

So, change AND to OR or remove the last line entirely (or put default values in the contact_coms.contact_id field so that it will return something)

[a-scripts.com]

I think 'OR' won't help you there cause you will most likely get some unwanted results. Personally I would try to go with LEFT JOIN there ..

 

SELECT ... FROM contacts JOIN clients ON ( contacts.client_id = clients.contact_id ) LEFT JOIN contact_coms ON ( contacts.contact_id = contact_coms.contact_id )

 

not exactly sure if this is what you want but I think you want to play with LEFT JOINs a bit

[wmguk]

thank you for that, i was looking at the left join, but wow, how confusing is this?!

 

basically I need to:

 

show all users in the clients table where clytpe = 2

i then need to check the clients table for the client id.

then check the contacts id to show contacts where client_id = client_id

then check the coms table where contact_id = contact_id....

 

so im using clients.cltype, clients.client_id, contacts.client_id, contacts.contact_id, contact_coms.contact_id

 

but i just dont see how to link them all...

 

I've now got

 

$sql = "SELECT clients.company, clients.client_id, contacts.client_id, contacts.name, contacts.contact_id, contact_coms.contact_id, contact_coms.com_content ".
"FROM clients, contacts, contact_coms ".
"JOIN clients ON ( clients.client_id = contacts.client_id ) LEFT JOIN contact_coms ON ( contacts.contact_id = contact_coms.contact_id ) ".
"AND clients.cltype='2'";

 

I get an error using that: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/domainname.co.uk/httpdocs/contacts/main2.php on line 56

 

[taquitosensei]

Try this

$sql = "SELECT clients.company, clients.client_id, contacts.client_id, contacts.name, contacts.contact_id, contact_coms.contact_id, contact_coms.com_content ".
"FROM clients JOIN contacts on clients.client_id=contacts.client_id LEFT JOIN contact_coms on contacts.contact_id=contact_coms.contact_id where clients.ctype=2";

the easiest way to think of a left join is, you're selecting all records on the table listed to the left of "LEFT JOIN"  and the table listed to the right of LEFT JOIN will show an empty record if there isn't a match.

 

 

[wmguk]

excellent thank you

 

my code is now

$sql = "SELECT clients.company, clients.address, clients.postcode, clients.date, clients.postcode, clients.comments, clients.sector, clients.level, clients.nextactivity, contacts.tel, contacts.mob, contacts.email, contacts.a_accmgr, contact_coms.actiondate, contact_coms.timestamp, clients.client_id, contacts.client_id, contacts.name, contacts.contact_id, contact_coms.contact_id, contact_coms.com_content ".
"FROM clients JOIN contacts on clients.client_id=contacts.client_id LEFT JOIN contact_coms on contacts.contact_id=contact_coms.contact_id " .$where. " AND clients.cltype=2 ORDER BY ".$qry;

 

can i add in another check... get the name of the account manager from users.name where users.username = contacts.a_accmgr

 

do i just add

 

LEFT JOIN users on users.username = contact.a_accmgr ?

[wmguk]

oh, I just noticed that also if there are two entries in the coms table, it displays two results rows for the client who has two entries...

 

how can i stop this duplication happening?

[wmguk]

and i've noticed that if there are no rows in the comms table then it loses the variables for the other companies....