SQL Server：只加入一个表 [英] SQL Server : join only one table

问题描述

假设我有四张桌子。

Let's say that I have those four tables.

PEOPOLE

ID NAME     SURNAME  COMPANY   UNIT   GROUPS
--------------------------------------------   
1  Michael  Backer      1        1      1
2  Travis   Morgan      2        2      2 
3  George   Marshall    3        3      3

COMPANY

ID NAME
------------
1  Coca Cola
2  Pepsi
3  Sprite

WORKUNIT

ID NAME
-------------    
1  Finances
2  Marketing
3  Sales

GROUPS (both values can be null)

ID NAME          FLOOR
-------------------------  
 1 Risks           5
 2 NULL           NULL
 3 Secretariat    NULL

Expected results

NAME SURNAME COMPANYNAME WORKUNIT GROUPS FLOOR
-----------------------------------------------    
Michael Backer Coca Cola Finances  Risks  5
Travis  Morgan Pepsi Marketing NULL NULL
George Marshall Sprite Sales Secretariat NULL
So far I write this query with no success:

SELECT
    people.NAME, people.SURNAME, company.NAME,
    workunit.NAME, groups.NAME, groups.FLOOR
FROM
    company, workunit, groups, people
LEFT JOIN
    groups on people.GROUP = GROUPS.id
WHERE
    company.id = people.company AND
    workunit.id = people.unit AND
    groups.id = people.group AND
    groups.floor = 'something from textbox';

我不熟悉组合连接语句在多个表中，所以请帮助我，因为我被卡住了。

推荐答案

你只需再次使用连接短语并移动一些WHERE子句是ON定义...

You just use the join phrase again and move some of the WHERE clause to be the ON definition...

SELECT
    people.NAME, people.SURNAME, company.NAME,
    workunit.NAME, groups.NAME, groups.FLOOR
FROM
    company
LEFT JOIN people on people.COMPANY=company.ID
LEFT JOIN workunit on people.UNIT=workunit.ID
LEFT JOIN groups on people.GROUP = GROUPS.id
WHERE
    groups.floor = 'something from textbox';

请注意，[people]上的连接必须在使用该表中信息的任何连接之前。



这个链接很好< a href =http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins> SQL连接的可视化表示 [ ^ ]



以下是关于联接的更多信息 http://www.w3schools.com/sql/sql_join.asp [ ^ ]



最后，请注意直接使用来自用户输入的数据你的sql语句，因为它让你容易受到sql注入 - 请参考 http://www.dotnetperls.com/sqlparameter [ ^ ]

Notice that the join on [people] has to come before any joins that use information from that table.

This link is a nice Visual Representation of SQL Joins[^]

And here is some further information on joins http://www.w3schools.com/sql/sql_join.asp[^]

Lastly, beware of using data from user input directly in your sql statement as it leaves you vulnerable to sql injection - reference here http://www.dotnetperls.com/sqlparameter[^]

SELECT
    P.NAME, P.SURNAME, C.NAME,
    W.NAME, G.NAME, G.FLOOR
FROM PEOPOLE  as P
join WORKUNIT  as W
on P.UNIT=W.ID
 join COMPANY as C
on C.ID=P.COMPANY
 join GROUPS as G
on P.GROUPS=G.ID

这篇关于SQL Server：只加入一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！