CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
James
James

Reputation: 2246

Joining these tables

I have 3 tables as below :

Table Name :
------------
UserList            

Column Name
-------------
DealerID        DealerUserID

AAA             111
AAA             222
AAA             333
BBB             111
BBB             444
CCC             111
CCC             555

--

Table Name :
------------
UserInfo        

Coulmns
--------
DealerUserID    Name    
111             John        
222             James
333             Dany
444             Daniel
555             Romie

--

Table Name :
------------
CarPermitted    

Coulmns
--------
DealerID        DealerUserID

AAA             111
AAA             222
BBB             111
CCC             111

I want a result as below of a query which will take input as :

For DealerID = AAA

Name    DealerUserID    AllowedStatus

John    111         true
James   222         true
Dany    333         false

I have tried many joins as below, but could not get my desired result. Any suggestion how can I get it.

AllowedStatus is the value I need to fetch as :

If the combination of DealerID and DealerUserID from table UserList is present in CarPermitted///rest will be false..

Note : It will display all the dealeruserId belongs to one dealer

Upvotes: 0

Views: 76

Answers (1)

Hogan
Hogan

Reputation: 70538

Here you go, you don't show your queries so I can say how you were going wrong

From the comments, Allowed status comes from if the record exists in the permitted table.

SELECT UF.Name, UF.DealerUserID, 
       CASE P.DealerID IS NULL THEN 'false' ELSE 'true' END AS AllowedStatus
FROM UserList UL
JOIN UserInfo UF ON UL.DealerUserID = UF.DealerUserID
LEFT JOIN CarPermitted P ON UL.DealerUserID = P.DealerUserID AND UL.DealerID = P.DealerID
WHERE UL.DealerID = 'AAA'

Upvotes: 1

Related Questions