CODEWITHSUNDEEP
sqlsql-server
rohit
rohit

Reputation: 67

Join tables with same keys, second table has multiple values for key and rows of second table must have same column value

I have two tables with shared key and I'm trying to join them to filter data based on few conditions

tbl1

id    | OutPutValue  | 
1     | 2019         |
2     | 2018         |

tbl2

object_id      | status  |  type |
1              | 22      |  a    |
1              | 22      |  c    |
1              | 33      |  b    |
2              | 33      |  c    |
2              | 33      |  c    |
2              | 33      |  c    |

What I'm trying to get is : it must select all 'OutPutValue' from tbl1 where, in tbl2 column 'type' should be c, and column 'status' must have same value for all rows i.e. 33. Note that Primary key (id) of tbl1 is foreign key (object_id) in tbl2.

Select column from tbl1 if, All rows in tbl2 (id of tbl1 have multiple rows (based on object_id) in tbl2) have same status value i.e. 33 and Type should be 'c'.

OutPutValue    | Type   |  status  |
2018           | c      |  33      |

I have tried with following solution, but it's not returning desired output : 

SELECT a.OutPutValue FROM tbl1 a JOIN tbl2 b ON a.id = b.object_id WHERE b.Type =c
GROUP BY a.OutPutValue, b.status HAVING b.STATUS IN(33)

Upvotes: 0

Views: 2137

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

Try a join combined with an aggregation:

SELECT
    t1.OutPutValue,
    MAX(t2.type) AS type,
    MAX(t2.status) AS status
FROM tbl1 t1
INNER JOIN tbl2 t2
    ON t1.id = t2.object_id
GROUP BY
    t1.id,
    t1.OutPutValue
HAVING
    MIN(t2.status) = MAX(t2.status) AND
    MAX(t2.status) = 33 AND
    MIN(t2.type) =  MAX(t2.type) AND
    MAX(t2.type) = 'c';

Upvotes: 1

Gosfly
Gosfly

Reputation: 1300

Another solution could be the following :

SELECT T1.id, T1.outputvalue FROM tbl1 T1
JOIN ( 
     SELECT tbl2.*, MAX(type), MAX(status)
     FROM tbl2
     GROUP BY object_id
     HAVING 
          MIN(status) = MAX(status) AND
          MIN(type) = MAX(type)
) T2 ON T1.id = T2.object_id
WHERE T2.type = 'c'

EDIT: I have updated my query to match a particular case which make it quite similar to another answer.

FIND A DEMO HERE

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

You can try using correlated subquery

DEMO

select distinct OutPutValue,type, status
from t2 a inner join t1 b on a.object_id=b.id
where type='c' and not exists 
      (select 1 from t2 a1 where a.object_id=a1.object_id and status<>33 and type='c')

OUTPUT:

OutPutValue type    status
2018        c         33

Upvotes: 1

Related Questions