Click here to Skip to main content
15,904,500 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
I need some help.
I have 4 tables called pr_projet (id_proj, id_client,lib_proj), 0_client (id_client, id_contact, nom_client), 0_contact (id_contact, nom_contact, id_client) and pr_contactprojet(id_contactproj, id_proj, id_contact)
I need to display a table where we have the lib_proj, nom_client, nom_contact.
My query :
SQL
SELECT pr_projet.lib_proj, 0_contact.nom_contact, 0_client.nom_client
FROM pr_projet, 0_contact, 0_client
WHERE pr_projet.id_client = 0_client.id_client
AND pr_projet.id_proj = 0_contact.id_proj
GROUP BY lib_proj

It displays only projects that have one or more contact(s) If my project does not have a contact it is not displayed. How can I do?

Thanks
Posted
Updated 2-Jul-13 1:53am
v4
Comments
Shafeequl 2-Jul-13 7:24am    
I think you have to use join
jaideepsinh 2-Jul-13 7:31am    
Why you not use join instead of this method.
scottgp 2-Jul-13 7:38am    
Not sure what database you're using, but it looks like you need an OUTER join
stephane1301 2-Jul-13 7:41am    
Thanks to all
I will try to use join

Try this, this might help

SQL
SELECT PP.lib_proj, CL.nom_client, CC.nom_contact
FROM pr_projet PP
INNER JOIN O_client CL ON PP.id_client = CL.id_client
LEFT OUTER JOIN O_contact CC ON CC.id_client = CL.id_client


Thanks.
 
Share this answer
 
HTML
if you want to use GROUP BY then you have to take all columns in group by.
 
Share this answer
 
v2
First, your AND statment:
AND pr_projet.id_proj = 0_contact.id_proj

use 0_contact.id_proj, but that field is not listed in the table structure, so I'm assuming it should be there.

Second, here is my best guess at to what will help.
SQL
SELECT pr_projet.lib_proj, 0_contact.nom_contact, 0_client.nom_client
from pr_projet left outer join 0_contact on pr_projet.id_proj = 0_contact.id_proj
left outer 0_client on pr_projet.id_client = 0_client.id_client


Also, since you do not have an aggregate field (count, sum, max, min), you do not need a group by clause.

Hope this helps.

Tim
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900