i have 3 tables (simplified version - whole picture bit more complex).
table: customer table: purchase1 table: purchase2 =============== ======================= ======================= customerid customerid | productid customerid | productid --------------- ------------|---------- ------------|---------- 1 1 | 51 1 | 81 2 1 | 52 1 | 82 3 2 | 52 1 | 83 i know table structure isn't best that's not need with. products held in purchase tables of different types, if helps provide context.
i'm trying join tables, using query this:
select customer.customerid, purchase1.productid p1, purchase2.productid p2 customer left join purchase1 on customer.customerid = purchase1.customerid left join purchase2 on customer.customerid = purchase2.customerid customer.customerid = 1; this produces following:
customerid | p1 | p2 -------------------- 1 | 51 | 81 1 | 51 | 82 1 | 51 | 83 1 | 52 | 81 1 | 52 | 82 1 | 52 | 83 how instead?
customerid | p1 | p2 -----------|------|--- 1 | 51 | null 1 | 52 | null 1 | null | 81 1 | null | 82 1 | null | 83 the first table has row every combination of p1 , p2. second table has row each customer-product combination.
can without using union? reason ask, because query become more complex, using columns other rows aren't in purchase1 or purchase2.
if have use union, how can such can still select other tables , have additional columns in query?
use union . see demo. in union, have have same number of columns in both queries use null match number of column in both query
select * (select customer.customerid, purchase1.productid p1, null p2 customer inner join purchase1 on customer.customerid = purchase1.customerid union select customer.customerid, null p1, purchase2.productid p2 customer inner join purchase2 on customer.customerid = purchase2.customerid) tb tb.customerid = 1; 
Comments
Post a Comment