lookup table query

Question

question

Sam 2 asked Oct 12, '09

lookup table query

Hi there

I am trying to find out the most efficient way of joining some tables.

Table 1 - Products
fields - ProductID, ProductName

Table 2 - Shops
fields - ShopsID, Shopname, ShopCity

Table 3 - ShopsProductsLookup
fields - ShopsID, ProductsID, Quantity

What I want to end up with is a stored procedure that you pass in a ShopID to and it returns a result set of all the products (no duplicates). Each row should contain the productID, productname and, if the product has a joining record in the ShopsProductsLookup table for the passed in ShopID the row should also contain a 'true' marker (yes the product is that shop) and the quantity of that product type in that shop. If the product is not in the ShopsProductsLookup for that passed in ShopID it should instead have 'false' in the 'yes product is in the shop' column and quantity can be zero.

So I'm hoping to end up with a duplicate free result set containing something like

1 Shoes true 10
2 Hats false 0
3 Pants true 5
4 Shirts true 1

etc.

So far I can only get a result set back with duplicate rows like

1 Shoes true 10
1 Shoes false 3 (this row is because another shop has 3 shoes)
etc

(unless I do multiple sub-queries, but I assume that is not the best way performance wise as particularly given these table may get quite large)

Any help would be greatly appreciated.

sql-server-2005 stored-procedures select joins

10 |1200

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

Answer 1 · 2009-10-12T09:04:31Z

RickD answered Oct 12, '09

You might be after something like this:

create table #Products (ProductID int, ProductName varchar(10))            
create table #Shops (ShopsID int, Shopname varchar(10), ShopCity varchar(10))            
            
create table #ShopsProductsLookup (ShopsID int, ProductID int, Quantity int)            
            
insert into #Products            
select 1,'shoes'            
union select 2,'hats'            
            
insert into #Shops            
select 1,'yak','yak'            
union select 2,'greatyak','greatyak'            
            
insert into #ShopsProductsLookup            
select 1,1,3            
union select 1,2,4            
union select 2,1,1            
            
            
select              
    p.ProductID,             
    p.ProductName,             
    case when spl.ShopsID is null then 'false' else 'true' end as InShop,            
    sum(case when spl.ShopsID is null then 0 else Quantity end) as Quantity            
from #Products p            
left join #ShopsProductsLookup spl            
    on spl.ProductID = p.ProductID            
left join #Shops S            
    on s.ShopsID = spl.ShopsID            
group by            
    p.ProductID,             
    p.ProductName,             
    case when spl.ShopsID is null then 'false' else 'true' end            
            
drop table #Products            
drop table #Shops            
drop table #ShopsProductsLookup

I am guessing that you want this for a business stock count and therefore do not care which shop the product is in.

The only other way to do this is to add the ShopID into your query and then you could say whether a shop requires more of a certain stock.

10 |1200

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

Answer 2 · 2009-10-12T20:52:33Z

Sam 2 answered Oct 12, '09

Thanks RickD

Based on the above, and my actually thinking about what the aggregate functions are doing in realtion to the group by cause (i.e. getting rid of MIN() that was causing my true false column to be wrong) I have it solved.

10 |1200

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

question