开发者

Dont enforce one of conditions in MySQL Query.

开发者 https://www.devze.com 2023-03-24 04:50 出处:网络
I have 3 tables: **people** ID First Last EmailWageIDShiftID 1 john Smith jhn@mail.com23 2 sara doesar@mail.com1<----- no number

I have 3 tables:

**people**
ID First Last Email        WageID  ShiftID
1 john Smith jhn@mail.com    2       3
2 sara doe   sar@mail.com    1         <----- no number



**wages**
ID Wage
1 300
2 500
3 800



**shifts**
ID Shift
1 day
2 night
3 half

I need to make one query for to get info from all tables I have:

SELECT people.ID people.First people.Last people.Email wages.Wage, shifts.Shift 
FROM people, wages, shifts 
WHERE people.wageID = wages.ID
AND people.shiftID = shifts.ID

Problem is that because "Sara Doe" does not have shift id specified it开发者_高级运维 does not show her i think because of last like of select query is there way to make working query to show all people without altering tables?

If i try OR people.shiftID = shifts.ID it still dont show her


You should use OUTER JOIN for this

SELECT people.ID, people.First, people.Last, people.Email, wages.Wage, 
shifts.Shift FROM people
JOIN wages ON people.wageID = wages.ID
OUTER JOIN shifts ON people.shiftID = shifts.ID


You should use some left joins :

select people.ID people.First people.Last people.Email wages.Wage, shifts.Shift
from people
    left join wages on wages.ID = people.WageID
    left join shifts on shifts.ID = people.ShiftID
0

精彩评论

暂无评论...
验证码 换一张
取 消