Home Many-to-many that shows columns with null

# Many-to-many that shows columns with null

gdubs
1#
gdubs Published in 2018-01-11 07:58:32Z
 I'm having some difficulty trying to figure how to adjust my query. I'm not very good at SQL queries as it's not my forte. Anyway, I'm not sure what I'm doing wrong. Here's my table setup. ID | Customer ---+------------- 1 | John 2 | Jane 3 | Steve ID | Assets ---+------------- 1 | RealEstate 2 | Currency 3 | Stocks CustomerID | AssetConfigurationId | Status -----------+----------------------+------- 1 | 1 | E 1 | 2 | F 1 | 3 | X 2 | 3 | X  And if I query customer = 3, I want to get the following AssetConfigurationId | Status ---------------------+------------ 1 | null 2 | null 3 | X  Currently have this. I'm trying to understand how I can use left join to show all the assets and just have the values of the statuses to null for a specific customer. Right now it only shows the 3rd row. Trying to do this in a SQL Server stored procedure so that my .net application can get a list of the assets already and I'll just modify the statuses when it comes to converting them to objects. select ac.Id, r.Status from assets ac left join assets_ref r on r.AssetConfigurationId = ac.Id where r.CustomerID = 3 
Vashi
2#
Vashi Reply to 2018-01-11 08:07:56Z
 Move your WHERE condition in the inner query. select ac.Id, r.Status from assets ac left join (select * from assets_ref where CustomerID = 3) r on r.AssetConfigurationId = ac.Id; 
Justinas Marozas
3#
Justinas Marozas Reply to 2018-01-11 10:41:52Z
 You can use multiple conditions in JOINs: select ac.Id, r.Status from assets ac left join assets_ref r on r.AssetConfigurationId = ac.Id and CustomerID = 3; 
 You need to login account before you can post.
Processed in 0.299036 second(s) , Gzip On .