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

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.

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

© 2016 Powered by mzan.com design MATCHINFO