question

harry1991 avatar image
harry1991 asked

CTE Query

![alt text][1] [1]: /storage/temp/3384-problem1.png
sql-server-2008sql-serversql server 2012sql querycte
problem1.png (27.4 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is a duplicate of SQL Joins
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Your update is working when the company is in Table2. All other rows in Table1 are not affected. You need to add the missing companies to Table2. PS: Honda makes cars and bikes. You might need to allow for that.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So does Suzuki...
0 Likes 0 ·
KenJ avatar image
KenJ answered
I think your model needs a small adjustment. I would pull the company out of table1 and put the default price for each product into table1 since you already have a column for it that isn't being used. For this example, I'm using slightly different table names with #categories = table1 and #brands= table2 create table #categories( category_name nvarchar (400) not null, demand money not null); insert #categories(category_name, demand) values('bike', 25.00), ('car', 59.00); create table #brands( brand_name nvarchar(400) not null, category_name nvarchar(400) not null, demand money null); insert #brands(brand_name, category_name, demand) values ('honda', 'bike', null), ('suzuki', 'bike', null), ('hero', 'bike', null), ('ktm', 'bike', 25), ('davidson', 'other', 35), ('nissan', 'car', 59), ('renault', 'car', null); -- this query illustrates the problem with your update. some brands get a null price (demand) select a.category_name, b.brand_name, b.demand as brand_price from #categories as a inner join #brands as b on a.category_name = b.category_name; /* category_name | brand_name | brand_price ---------------------------------------------- bike | honda | NULL bike | suzuki | NULL bike | hero | NULL bike | ktm | 25.00 car | nissan | 59.00 car | renault | NULL */ -- using the category price, we can use coalesce (or isnull) to push a price down to any brands that have not overridden the default price (demand) select a.category_name, b.brand_name, coalesce(a.demand, b.demand) as brand_price from #categories as a inner join #brands as b on a.category_name = b.category_name; /* category_name | brand_name | brand_price ---------------------------------------- bike | honda | 25.00 bike | suzuki | 25.00 bike | hero | 25.00 bike | ktm | 25.00 car | nissan | 59.00 car | renault | 59.00 */ drop table #categories; drop table #brands;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.