Select result from multi tables with joins

Question

I have this query. I want to select the

surgery Type
surgery Details
Modality ID

Am I on the right path atleast?

The LINK table is the middle of everything

Addmission 1toM on LINK
Surgery Mto1 on LINK
Modality Mto1 on LINK

My problem is just add in Modality.ID to the result.
skip modality, and the query works 100%

updated (dropped multi alias)

SELECT surg.srg_Details, surg.Type, modd.ID
FROM Surgery surg
JOIN LINK lnk on lnk.lnk_ID = surg.srg_lnkID
JOIN Modality modd ON modd.mod_lnkID = lnk.lnk_ID
JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE  adm.adm_ID = 192100042

Please show sample sample data and desired results. A non-working query doesn't do much to convey what you really need to do. — Gordon Linoff, Apr 01 '15 at 12:37
yikes, how is this even running?, you are joining `Modality` twice, and with the same alias even — Lamak, Apr 01 '15 at 12:37
@Lamak. fair point. I did try it without, but it remains a "General Error", what ever that implies. Mod is also a reserved word, so opted for `modd` — morne, Apr 01 '15 at 13:00

Stanislovas Kalašnikovas · Accepted Answer · 2015-04-01T13:16:25.233

1

Try to use in this case:

SELECT surg.srg_Details, surg.Type, mod.ID
FROM Surgery surg, Modality mod
JOIN LINK lnk on lnk.lnk_ID = surg.srg_lnkID
JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE  adm.adm_ID = 192100042 AND surg.srg_lnkID = mod.mod_lnkID

edited Apr 01 '15 at 13:16

answered Apr 01 '15 at 12:40

Stanislovas Kalašnikovas

9,203
4
32
59

Im using RazorSQL and it only gives me a "General error". so Im not sure why. I had it like you have here, but same thing happens. – morne Apr 01 '15 at 12:45
What database are you using? – Stanislovas Kalašnikovas Apr 01 '15 at 12:51
4th Dimension's SQL Server. – morne Apr 01 '15 at 12:58

score 0 · Answer 2 · answered Apr 01 '15 at 12:48

Try to use left Join though no match null value will be present

SELECT surg.srg_Details, surg.Type, mod.ID
FROM Surgery surg, Modality mod
left JOIN LINK lnk on lnk.lnk_ID = surg.srg_lnkID
left JOIN Modality mod ON mod.mod_lnkID = lnk.lnk_ID
left JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE  adm.adm_ID = 192100042

Select result from multi tables with joins

2 Answers2