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

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

i have simple request sql server 2008, can't success achive want. explanation have next schema :

for given favorite, user can have 0 or many comments, however, if user have more 1 comment, comments have deleteddate not null (except perchance lastly one). deleteddate acts flag "isdeleted", application ensures 0 or 1 comment per user per favorite null. if deleteddate null, record considered deleted.

but user can have favorite without comment. comment totally optional. what i'd like

i'm trying create request, which, given user, homecoming valid favorites (where favorite.deletedate not null) , valid comment associated favorited (if exists).

here request :

select * favorite f left bring together comment co on f.idreferenced = co.idreferenced f.iduser = 7 , f.deleteddate null , co.iduser = 7

(please utilize iduser = 7 test)

however, request returns comments of favorites of user 7, comments deleteddate not null.

i tried add together and co.deleteddate not null above request, now, didn't homecoming favorites haven't comment fiddle

to reproduce problem, created sqlfiddle

attempted results if wasn't clear in explanations, request must homecoming 4 rows of sqlfiddle, commenttext field "must appear 1", "must appear 2", "must appear 3", "must appear 4"

the request must homecoming above lines, minus red-crossed line

important made error in sqlfiddle, 4th 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 datetime), null, null)

could please improve request ? i'll have write in linq, should able traduce sql linq. give thanks time !

if understand requirement need set status right table (co.deleteddate not null) in join portion of query like:

select * favorite f inner bring together comment co on f.idreferenced = co.idreferenced , co.deleteddate null , co.iduser = f.iduser f.iduser = 7 , f.deleteddate null

edit: in sql fiddle row below had value inserted in deleteddate. if value null above query gives desired result. have changed 'left join' 'inner join' in order not show different users.

insert [dbo].[comment] ([idcomment], [iduser], [idreferenced], [commenttext], [creationdate], [modificationdate], [deleteddate]) values (8, 7, 2869, n'must appear 3', cast(0x0000a33500ec1133 datetime), null, null)

sql sql-server sql-server-2008 left-join sqlfiddle