Click here to Skip to main content
11,933,854 members (56,028 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL
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 :
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?

Posted 2-Jul-13 2:15am
Edited 2-Jul-13 2:53am
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
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try this, this might help

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

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

if you want to use GROUP BY then you have to take all columns in group by.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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.
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.


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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.151126.1 | Last Updated 2 Jul 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100