Check which table has the wanted value in a column from multiple tables-CodePudding

Got 2-4 tables and all of them has "Product_Code" column.

I want to check which one of these tables has the "Product_Code" I'm looking for.

Code mostly only at one table. But in spesific cases it can be on two tables at the same time. Which I don't care which one it gives me. I just need a table that has the code.

I can do this with multiple SELECT queries but I was wondering if there is an easier way to do is.

Searched a bit but couldn't get more than join statesments.

Thanks in advance!

CodePudding user response：

You can use a query like this:

Select 'Table1TableName' as TableName, ProductCode from Table1 where ProductCode = @ProductCode
Union
Select 'Table2TableName' as TableName, ProductCode from Table2 where ProductCode = @ProductCode
Union
Select 'Table3TableName' as TableName, ProductCode from Table3 where ProductCode = @ProductCode
Union
Select 'Table4TableName' as TableName, ProductCode from Table4 where ProductCode = @ProductCode

This will give one row for every hit tablename of where the hit came from.

You can also use dynamic query, but I don't think it will be more efficient than this.

CodePudding user response：

Every device has 5-8 silos. Devices are basically a weigher. Every weigher has specific ingredients they can weigh but some frequently used ones might be in more than one weigher. Trying to figure which weigher's silos has that ingredient and in the recipe I am saying get that ingredient from that weigher. I am not "aware" of silos, all I know is weigher has ingredients. If it has the ingredient on one of it's silos it knows and pulls that ingredient.

Let's pretend there was an appropriate data model :-) This is basically what the query would look like then:

select *
from recipe r
join recipe_ingredients ri on ri.recipe_id = r.recipe_id
join weigher_ingredient wi on wi.ingredient_id = i.ingredient_id;

But that could give you ingredients multifold that are present in more than one weigher. We must pick one weigher per ingredient. One way to do this is a lateral join, called CROSS APPLY in SQL Server.

select *
from recipe r
join recipe_ingredients ri on ri.recipe_id = r.recipe_id
join lateral
(
  select top (1) *
  from weigher_ingredient
  where weigher_ingredient.ingredient_id = i.ingredient_id
) wi;

In your database there is not one weigher_ingredient table however, but one ingredient table per weigher. Let's call these weigher1_ingredient, weigher2_ingredient, and weigher3_ingredient. The query then becomes:

select *
from recipe r
join recipe_ingredients ri on ri.recipe_id = r.recipe_id
join lateral
(
  select top (1) *
  from
  (
    select * from from weigher1_ingredient
    union all
    select * from from weigher2_ingredient
    union all
    select * from from weigher3_ingredient
  ) weigher_ingredient
  where weigher_ingredient.ingredient_id = i.ingredient_id
) wi;

And as has been mentioned in the request comments, you can simply create weigher_ingredient as a view. Thus you wouldn't have to union all the tables in every query (i.e. you would use my second query instead of this third one).

It depends on SQL Server's optimizer, how good the execution plan becomes. Ideally, it will see that we are looking for one ingredient_id at a time, and hopefully there are indexes on this ID, so this row will quickly get fetched. The optimizer may further spot that it can stop searching once it finds the ingredients in one of the tables, because of TOP (1). If it doesn't yet, we can still hope for a future version that can :-)

CodePudding user response：

I think you can write dynamic sql query. For example

exec 'select * from ' @tableName ' where Product_Code=' @productCode ''

and you can use it in loop.