samedi 9 mai 2015

looking for master/child table data base on condition?

I have a 2 tables likes below,

DECLARE @MASTER TABLE (MAST_ID INT, MAST_NAME NVARCHAR(10), IS_ACTIVE CHAR(1))
INSERT INTO @MASTER VALUES (1, 'MAST1', 'A'), (2, 'MAST2', 'I'), (3, 'MAST3', 'A'), (4, 'MAST4', 'A')
SELECT * FROM @MASTER

DECLARE @CHILD TABLE (CHD_ID INT, MAST_ID INT, CHD_NAME NVARCHAR(10), IS_ACTIVE CHAR(1))
INSERT INTO @CHILD VALUES (1, 1, 'CHD1', 'I'), (2, 2, 'CHD2', 'A'), (3, 4, 'CHD3', 'A'), (4, 4, 'CHD4', 'I') 
SELECT * FROM @CHILD

  1. need all active master table data, which are active and for those there are active child data

  2. need all active child for above master data

Output should be like below Thanks for help.

enter image description here

Aucun commentaire:

Enregistrer un commentaire