Search code examples
sqlsql-serversql-server-2008left-joinsqlfiddle

SQL - Join 2 tables using a join column AND a condition

I have a very simple request to do with SQL Server 2008, but I can't success to achive what I want.

Explanation
I have the following schema :

Schema SQL

For a given FAVORITE, a user can have zero or many COMMENTs, however, if the user have more than one comment, all COMMENTs have a DeletedDate NOT NULL (except possibly the last one).

DeletedDate acts as a Flag "IsDeleted", the application ensures that only zero or one comment per user per favorite is NULL. If DeletedDate is NULL, the record is considered as deleted.

But a user can have a FAVORITE without COMMENT. COMMENT is totally optional.

What I'd like

I'm trying to create a request, which, for a given user, will return all its valid favorites (where FAVORITE.DeleteDate is NOT NULL) and the valid COMMENT associated to the FAVORITED (if it exists).

Here is my request :

SELECT *
FROM FAVORITE f
LEFT JOIN COMMENT co ON f.IdReferenced = co.IdReferenced
WHERE f.IdUser = 7
  AND f.DeletedDate IS NULL
  AND co.IdUser = 7

(Please use IdUser = 7 to test)

However, this request returns all COMMENTs of all FAVORITEs of user 7, even comments where DeletedDate is NOT NULL.

I tried to add a AND co.DeletedDate IS NOT NULL to the above request, but now, it didn't return FAVORITEs which haven't any COMMENT


FIDDLE

To reproduce the problem, I created this SQLFIDDLE



ATTEMPTED RESULTS

If I wasn't clear in my explanations, the request must return FOUR rows of my SQLFIDDLE, where the CommentText field is "Must appear 1", "Must appear 2", "Must appear 3", "Must appear 4"

Attempted result

The request must return the above lines, minus the red-crossed line

IMPORTANT I just made an error in my SQLFIDDLE, the fourth line of COMMENT should be

INSERT [dbo].[COMMENT] ([IdComment], [IdUser], [IdReferenced], [CommentText], [CreationDate], [ModificationDate], [DeletedDate]) VALUES (8, 7, 2869, N'Must appear 3', CAST(0x0000A33500EC1133 AS DateTime), NULL, NULL)

Could you please improve my request ? I'll have to write it in LINQ, but I should be able to traduce from SQL to LINQ.
Thank you for your time !

Solution

  • If I understand your requirement you need to put the condition for the right table (co.DeletedDate IS NOT NULL) in the JOIN portion of your query like:

    SELECT *
FROM FAVORITE f
INNER JOIN COMMENT co ON f.IdReferenced = co.IdReferenced
AND co.DeletedDate IS NULL
AND co.IdUser = f.IdUser
WHERE f.IdUser = 7
  AND f.DeletedDate IS NULL

    EDIT: In your sql fiddle the row below had a value inserted in DeletedDate. If this value is null the above query gives the desired result. Also have changed the 'LEFT JOIN' to 'INNER JOIN' in order to not show different users.

    INSERT [dbo].[COMMENT] ([IdComment], [IdUser], [IdReferenced], [CommentText], [CreationDate], [ModificationDate], [DeletedDate]) VALUES (8, 7, 2869, N'Must appear 3', CAST(0x0000A33500EC1133 AS DateTime), NULL, NULL)