CODEWITHSUNDEEP
sql
Dharmesh Gohel
Dharmesh Gohel

Reputation: 3

After Table Joining, need specific row value based on another Row with same ID

I have 2 tables as follows:

Table 1: 

ID  FName
1   Basics
2   Machine1
3   master
4   Machine2
15  Machine3
16  Machine16

Table 2: 

ParentID    Name            InitialValue
1       Active          1
2       MachineName     Entrylevel
2       Active          1
3       Active          1
4       MachineName     Midlevellevel
4       Active          1
15      MachineName     Endlevel
15      Active          1
16      MachineName     Miscellenious
16      Active          0

Here, ID of Table 1 is referred as Parent ID at Table 2. I want "Initial Value" of Table 2 for MachineName Rows (of Table 2) provided "InitialValue" of Table 2 for Active Rows (of Table 2) are 1

Result should be like

ID  InitialValue
2   Entrylevel
4   Midlevellevel
15  Endlevel

Upvotes: 0

Views: 38

Answers (3)

trincot
trincot

Reputation: 351158

You could join the second table twice, once for MachineName, and once for Active:

SELECT     t.ID, machine.InitialValue
FROM       table1 t 
INNER JOIN table2 machine 
        ON t.ID = machine.ParentId
       AND machine.Name = 'MachineName'
INNER JOIN table2 active 
        ON t.ID = active.ParentId
       AND active.Name = 'Active'
       AND active.InitialValue = 1;

About Joins

The JOIN syntax allows you to link records to the previous table in your FROM list, most of the time via a relationship of foreign key - primary key. In a distant past, we used to do that with a WHERE condition, but that really is outdated syntax.

In the above query, that relationship of primary key - foreign key is expressed with t.ID = machine.ParentId in the first case. Note the alias that was defined for table2, so we can refer to it with machine.

Some extra condition(s) are added to the join condition, such as machine.Name = 'MachineName'. Those could just as well have been placed in a WHERE clause, but I like it this way.

Then the same table is joined again, this time with another alias. This time it filters the "Active" 1 records. Note that if the ID in table1 does not have a matching record with those conditions, that parent record will be excluded from the results.

So now we have the table1 records with a matching "MachineName" record and are sure there is an "Active" 1 record for it as well. This is what needs to be output.

Upvotes: 2

Hector Jimeno
Hector Jimeno

Reputation: 31

SELECT t1.ID, t2.InitialValue
FROM table1 t1 join table2 t2 on t1.ID=t2.ParentID
WHERE t2.name LIKE 'MachineName'AND t1.ID= ANY(SELECT t22.ParentID
                                                  FROM table2 t22
                                                  WHERE t22.InitialValue=1)

I think this should work

//slightly changed the condition in WHERE clausule (t2.parentID changed to t1.ID)

Upvotes: 0

maxhb
maxhb

Reputation: 8865

Not sure if this is standard SQL but it should work using MySQL.

select T1.ID, T2.InitialValue
from Table1 T1 inner join Table2 T2 on T1.ID = T2.ParentId
where
T2.Name <> 'Active'
and exists (
  select * from Table2 T3 where T3.ParentId = T1.ID and T3.Name = 'Active' and T3.InitialValue = 1
)

Upvotes: 0

Related Questions