Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 :
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 2-Jul-13 2:15am
Edited 2-Jul-13 2:53am
RyanDev129.8K
v4
Comments
Shafeequl at 2-Jul-13 7:24am
   
I think you have to use join
jaideepsinh at 2-Jul-13 7:31am
   
Why you not use join instead of this method.
scottgp at 2-Jul-13 7:38am
   
Not sure what database you're using, but it looks like you need an OUTER join
stephane1301 at 2-Jul-13 7:41am
   
Thanks to all
I will try to use join
Rate this: bad
good
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
 
Thanks.
  Permalink  
Rate this: bad
good
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.
  Permalink  
v2
Rate this: bad
good
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.
 
Tim
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 215
1 George Jonsson 175
2 Kornfeld Eliyahu Peter 159
3 PIEBALDconsult 110
4 Richard MacCutchan 85
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 2 Jul 2013
Copyright © CodeProject, 1999-2014
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