CODEWITHSUNDEEP
sqlsql-server
NotAgain
NotAgain

Reputation: 1977

Is this possible to do using SQL only?

I have two tables. Table 1:

enter image description here

And Table 2:

enter image description here

The only thing I know about is ProductType Base which I use to get BatchId from Table1. Then I use all the BatchId's to get data from Table2.

More details: The Table1 has data from many ProductType 's. I am interested in Base. Once I get all the BatchId 's for the ProductType Base I use those to get data from Table2. Table2 also has data for many ProductType 's. However they did not store it. They just stored the BatchId 's. The trouble is that seal step is logged against another system generated BatchId for a given UnitNumber.

Right now my query looks like:

SELECT 
    Id, 
    AssemblyStep, 
    BatchId, 
    UnitNumber, 
FROM Table2 
WHERE BatchId IN (SELECT DISTINCT BatchId FROM Table1 WHERE ProductType='Base') 
ORDER BY Id ASC

This works as shown in the green rows but I am not able to get the two rows which I have highlighted in red.

enter image description here

This is expected. However is there a clever way I can extract the full data for UnitNumber 8888 and 9999 from Table2 using some joins?

Version: Microsoft SQL Server 2014

Upvotes: 0

Views: 80

Answers (4)

Luke Weaver
Luke Weaver

Reputation: 409

If you want the whole of table2 you just need a select * from table2

From what I see, table1 has no records where batchId = 222 so all table2 records with batchId 222 aren't supposed to show up.

EDIT

So if I understand this correctly, you also want table2 records where AssemblyStep = 'Seal' and UnitNumber is either 8888 or 9999

SELECT 
    Id, 
    AssemblyStep, 
    BatchId, 
    UnitNumber, 
FROM Table2 
WHERE BatchId IN (SELECT DISTINCT BatchId FROM Table1 WHERE ProductType='Base')
    OR
UnitNumber IN (Select DISTINCT UnitNumber FROM Table1 WHERE ProductType='Base') 
ORDER BY Id ASC

Upvotes: 1

Stu
Stu

Reputation: 32614

Looking at your sample data and output the joining condition is BatchId, so just this should work

select
    t2.Id, 
    t2.AssemblyStep, 
    t2.BatchId, 
    t2.UnitNumber
from
(select distinct unitnumber from Table1 where producttype='base') t1
join Table2 t2 on t2.unitnumber=t1.unitnumber

Upvotes: 0

ed2
ed2

Reputation: 1497

Simple solution

Right now, this will do what you want:

SELECT * 
FROM Table2 
ORDER BY Id ASC

It seems you have some logic in mind that you would like to match up some combination of batch ID and Product Type to form the foreign key, instead of just using Product Type (which cannot be the foreign key if it is not in Table2) or the Batch ID (which you describe as relevant in the question, but hint is not based on your desired result).

Selecting all actions for unit numbers returned by base:

If you want this: (1) Get the BatchId from Table 1 for all rows where ProductType is Base, (2) Get the rows from table 2 for that batch (3) Get all other rows from table 2 for the unit numbers generated by (2), regardless of the batch.

...with step (3) to ensure complete activities on any unit matching the "base" batch ID from table 1...

Then try this:

SELECT *
FROM Table2 
WHERE UnitNumber IN (SELECT UnitNumber FROM Table1 WHERE ProductType='Base')
ORDER BY Id ASC

Upvotes: 0

ShaiEitan
ShaiEitan

Reputation: 171

you should use LEFT JOIN instead of using WHERE Clause. LEFT JOIN gives you the rows that returned null from the left table, but exists in the right table therefore you won't miss any rows if the join not returned all the results.

SELECT 
    T2.Id, 
    T2.AssemblyStep, 
    T2.BatchId, 
    T2. UnitNumber, 
FROM Table2 T2
LEFT JOIN Table1 T1 ON T1.BatchId = T2.BatchId
ORDER BY T2.Id ASC

Upvotes: 0

Related Questions