CODEWITHSUNDEEP
sqljoin
user3451166
user3451166

Reputation: 189

What is wrong with the following SQL left join?

I have 5 tables: A, B, C, D, E

A: {ID, Value, GroupID, FilterID, In_Date}

B: {ID, Description, Out_Date, From_Date }

C: {ID, Category}

D: {GroupID, GroupName}

E: {FilterID, FilterName}

There could be missing IDs in B and C as a result, I'm trying to do a LEFT JOIN to get the required info. In my dataset, I've explicitly added a row in A with ID that's not in B or C. I expect it to be picked up in the following query, but it doesn't happen. I believe I'm going wrong in the WHERE clause due to null values in B and C. 

SELECT A.Value, A.Group, B.Description, C.Category, D.GroupName, E.FilterName 
FROM A LEFT JOIN B ON A.ID = B.ID     
       LEFT JOIN C ON A.ID = C.ID,
     D,E
WHERE
B.Out_Date>A.In_Date,
A.GroupID=D.GroupID,
A.FilterID=E.FilterID

How can I fix this to retrieve the fields I want with null values when the ID is not in B or C , but in A?

Upvotes: 0

Views: 262

Answers (4)

B. P. Singh
B. P. Singh

Reputation: 13

Here is your table details

A: {ID, Value, GroupID, FilterID, In_Date}

B: {ID, Description, Out_Date, From_Date }

C: {ID, Category}

D: {GroupID, GroupName}

E: {FilterID, FilterName}

now you try to retrieve data using left join so you try the flowing script

      SELECT A.Value, A.Group, B.Description, C.Category, D.GroupName,E.FilterName from A left join B on A.ID=B.ID
left Join C on A.ID=C.ID
Left Join D on A.GroupID=D.GroupID
Left Join E on A.FilterID=E.FilterID
where B.Out_Date>A.In_Date

I hope this is help full for you.

Upvotes: 1

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

You can use this query for your problem.

SELECT     A.Value, A.Group, B.Description, C.Category, D.GroupName, E.FilterName 
FROM       A 
LEFT JOIN  B 
   ON  A.ID = B.ID 
   AND B.Out_Date > A.In_Date
LEFT JOIN  C 
    ON A.ID = C.ID
LEFT JOIN D
    ON A.GroupID = D.GroupID
LEFT JOIN E
    ON A.FilterID = E.FilterID

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

First point: You shouldn't mix join syntaxes, just use explicit syntax.

Because of your where clause, you effectively turn your left joins in inner joins. You are probably looking for:

SELECT     A.Value, A.Group, B.Description, C.Category, D.GroupName, E.FilterName 
FROM       A 
LEFT JOIN  B 
       ON  A.ID = B.ID 
       AND B.Out_Date > A.In_Date
LEFT JOIN  C 
        ON A.ID = C.ID
INNER JOIN D
        ON A.GroupID = D.GroupID
INNER JOIN E
        ON A.FilterID = E.FilterID

Upvotes: 1

jarlh
jarlh

Reputation: 44795

1) Don't mix old comma separated join syntax with modern explicit join syntax!

2) When left join, put the right side table's conditions in the ON clause to get true left join behavior. (When in WHERE you get inner join result.)

SELECT A.Value, A.Group, B.Description, C.Category, D.GroupName, E.FilterName 
FROM A LEFT JOIN B ON A.ID = B.ID AND B.Out_Date > A.In_Date
       LEFT JOIN C ON A.ID = C.ID
       JOIN D ON A.GroupID = D.GroupID
       JOIN E ON A.FilterID = E.FilterID

Value and Group are reserved words in ANSI SQL, so you may need to delimit them, as "Value" and "Group".

Upvotes: 1

Related Questions