Home Many-to-many that shows columns with null
Reply: 0

Many-to-many that shows columns with null

user1424
1#
user1424 Published in July 18, 2018, 10:30 pm

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303703 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO