CODEWITHSUNDEEP
sql-servert-sqlsql-view
workvact
workvact

Reputation: 685

SQL Server : select row if column value is not null from two rows

I am trying to create a view for data from line items. My table entries look like these

item_type_id,item_type_name
1,I001
2,I002

item_model_id,item_model_name,item_type_id
1,I001M001,1
2,I001M002,1
3,I001M003,1
4,I002M001,2
5,I002M002,2
6,I002M003,2

item_price_id,item_type_id,item_model_id,item_price
1,1,NULL,149.99
2,2,NULL,249.99
3,1,3,199.99
4,2,6,299.99

cart_line_id,cart_id,item_model_id,quantity
1,1,3,5
2,1,1,15

The view I created is

create view dbo.vw_cart_line_items 
as
    select 
        cl.cart_line_id, cl.item_model_id, ipr.item_price_id, 
        itp.item_type_name, imd.item_model_name, cl.quantity, ipr.item_price
    from 
        cart_lines cl
    inner join 
        item_models imd on cl.item_model_id = imd.item_model_id
    inner join 
        item_types itp on imd.item_type_id  = itp.item_type_id
    left outer join 
        item_prices ipr  on ipr.item_type_id = imd.item_type_id 
                         and (ipr.item_model_id = imd.item_model_id or 
                              ipr.item_model_id is null)
go

The view result is

cart_line_id,item_model_id,item_price_id,item_type_name,item_model_name,quantity,item_price
1,3,1,I001,I001M003,5,149.99
1,3,3,I001,I001M003,5,199.99
2,1,1,I001,I001M001,15,149.99

The entry with item_model_id = 3 and item_price_id = 1 does not apply to the selected model as there is a specific entry item_model_id = 3 and item_price_id = 3 for it. The other model does not have a specific price, so it got the generic price. How can I update this query to exclude the row with item_model_id = 3 and item_price_id = 1 from the output?

Upvotes: 0

Views: 1998

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Ok, I see what you're trying to do.

One way to do it is to make your join to item_prices like this:

left outer join item_prices ipr  
on ipr.item_price_id = (
 SELECT TOP 1 item_price_id
 FROM item_prices ipr2
 WHERE ipr2.item_type_id=imd.item_type_id 
 and (ipr2.item_model_id = imd.item_model_id or ipr2.item_model_id is null)
 ORDER BY CASE WHEN ipr2.item_model_id = imd.item_model_id THEN 0 ELSE 1 END ASC
)

Joining to the table on the table's PK using a TOP 1 subquery guarantees you only get one row from the joined table, and then you use ORDER BY to set the precedence of which row you want to have priority.

Upvotes: 2

Related Questions