need help on an SQL query

Question

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 2-Jul-13 1:15am

stephane1301

Updated 2-Jul-13 1:53am

ZurdoDev

v4

Add a Solution

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

3 solutions

Add a Solution

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

_Asif_ · Answer 1 · 2013-07-02T02:23:00

Solution 1

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.

Posted 2-Jul-13 2:23am

_Asif_

**Nirav Prabtani** · Answer 2 · 2013-07-02T02:24:00

Solution 2

HTML

if you want to use GROUP BY then you have to take all columns in group by.

Posted 2-Jul-13 2:24am

Nirav Prabtani

Updated 2-Jul-13 2:25am

v2

Tim Carmichael · Answer 3 · 2013-07-02T02:34:00

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

need help on an SQL query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

need help on an SQL query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us