需要SQL查询的帮助 [英] need help on an SQL query
问题描述
您好,
我需要一些帮助。
我有4个名为pr_projet的表(id_proj,id_client,lib_proj),0_client(id_client,id_contact,nom_client),0_contact (id_contact,nom_contact,id_client)和pr_contactprojet(id_contactproj,id_proj,id_contact)
我需要显示一个表格,其中包含lib_proj,nom_client,nom_contact。
我的查询:
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
推荐答案
试试这个,这可能会有所帮助
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.
if you want to use GROUP BY then you have to take all columns in group by.
首先,您的AND声明:
First, your AND statment:
AND pr_projet.id_proj = 0_contact.id_proj
使用0_contact.id_proj,但是那个fi字段没有在表格结构中列出,所以我认为它应该在那里。
其次,这是我最好的猜测是什么有用。
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
另外,由于你没有聚合字段(count,sum,max,min),你不需要group by子句。
希望这会有所帮助。
Tim
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
这篇关于需要SQL查询的帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!