CODEWITHSUNDEEP
sql
akhrot
akhrot

Reputation: 445

Complexity in a join

Can anyone plese help me how can i join these table, i have used outer join but i am not sured what should i put in select statement. i don't want to put wildcard. Please help.

Table

DECLARE @MASTER TABLE

(
SKU VARCHAR (30),
Resistor_ID VARCHAR (30),
Capacitor_ID VARCHAR (30),
Inductor_ID VARCHAR (30)
)
INSERT @MASTER

SELECT 'AREN2XS', '101',    '1F01', 'H561' UNION ALL
SELECT 'GTYO63K','201', '2F02', 'H562' UNION ALL
SELECT 'LKUTN58','301', '3F03', 'H563' UNION ALL
SELECT 'POILOI44','401',    '4F04', 'H564' UNION ALL
SELECT 'GTYUIO99','501',    '5F05', 'H565' UNION ALL
SELECT 'AREN2XS','101', '1F01', 'H561' UNION ALL
SELECT 'LKUTN58','301', '3F03', 'H563' union ALL
select 'KPNGHT39','','',''



DECLARE @RESISTOR_CHILD TABLE
(
Resitor_ID  VARCHAR (30),
Resistor_Value VARCHAR (30)
)

INSERT @RESISTOR_CHILD

SELECT '101','10OHM' UNION ALL
SELECT '301','30OHM'UNION ALL
SELECT '401','40OHM'UNION ALL
SELECT '501','50OHM'

DECLARE @CAPACITOR_CHILD TABLE
(
Capacitor_ID VARCHAR (20),
Capacitor_Value VARCHAR (20)
)

INSERT @CAPACITOR_CHILD
SELECT '2F02',  2UF UNION ALL
SELECT '3F03',  3UF UNION ALL
SELECT '5F05',  5UF UNION ALL
SELECT '5F05',  5UF 

DECLARE @INDUCTOR_CHILD TABLE
(
Inductor_ID VARCHAR (20),   
Inductor_valuue VARCHAR (20)
)

INSERT @INDUCTOR_CHILD

SELECT 'H561',  '1HEN' UNION ALL
SELECT 'H562',  '2HEN' UNION ALL
SELECT 'H562',  '2HEN' UNION ALL
SELECT 'H563',  '3HEN' UNION ALL
SELECT 'H564',  '4HEN'

--Expected Output

/* --Expected Output

/*

SKU,    Resistor_ID,Resistor_Value,Capacitor_ID,Inductor_ID 


AREN2XS,    101,    10OHM,  1F01,   ''  ,H561   ,1HEN

GTYO63K ,   201,    ''   ,  2F02,   2UF ,H562   ,2HEN

LKUTN58,    301,    30OHM,  3F03,   3UF ,H563   ,3HEN

POILOI44,   401,    40OHM,  4F04,   ''  ,H564   ,4HEN

GTYUIO99,   501,    50OHM,  5F05,   5UF ,H565   ,''

KPNGHT39,  '',     '',     '',      '', '',      ''

*/

Upvotes: 0

Views: 355

Answers (4)

sagi
sagi

Reputation: 40491

You need left outer joins like this:

SELECT distinct t1.sku,t2.resistor_id,t2.resistor_value,t3.capacitor_id,t4.inductor_id
FROM master t1
LEFT OUTER JOIN resistor_child t2 ON(t1.resistor_id = t2.resistor_id)
LEFT OUTER JOIN CAPACITOR_child t3 on(t1.capacitor_id = t3.capacitor_id)
LEFT OUTER JOIN inductor_child t4 on(t1.inductor_id = t4.inductor_id)

That will join all the table together, and the left join will put null values in those columns that doesn't have value matched in one of the 3 tables (resistor_child , CAPACITOR_child ,inductor_child )

Upvotes: 2

Shukri Gashi
Shukri Gashi

Reputation: 535

Try this statement 

SELECT DISTINCT m.SKU,m.Resistor_ID, r.Resistor_Value,m.Capacitor_ID, c.Capacitor_Value,m.Inductor_ID,
           i.Inductor_valuue
      FROM @MASTER m LEFT OUTER JOIN @RESISTOR_CHILD r ON m.Resistor_ID=r.Resitor_ID LEFT JOIN @CAPACITOR_CHILD AS c ON m.Capacitor_ID=c.Capacitor_ID
      LEFT OUTER JOIN @INDUCTOR_CHILD AS i ON m.Inductor_Id=i.Inductor_ID

Upvotes: 1

Alex
Alex

Reputation: 21766

You need to use LEFT JOIN to join your MASTER table to the three child tables and use ISNULL or COALESCE to replace any NULL values with an empty string:

SELECT  SKU ,
       m.Resistor_ID ,
       ISNULL(rc.Resistor_Value,'') AS Resistor_Value,
       ISNULL(cc.Capacitor_ID, '') AS  Capacitor_ID,
       ISNULL(ic.Inductor_ID,'') AS Inductor_ID,
       ISNULL(ic.Inductor_valuue,'') AS Inductor_Value
       --,COALESCE(ic.Inductor_valuue,'') AS Inductor_Value --You can use COALESCE as well
FROM       @Master m
       LEFT  JOIN @RESISTOR_CHILD rc ON m.Resistor_ID = rc.Resitor_ID
       LEFT JOIN @CAPACITOR_CHILD cc ON cc.Capacitor_ID = m.Capacitor_ID
       LEFT JOIN @INDUCTOR_CHILD ic ON ic.Inductor_ID = m.Inductor_ID

Upvotes: 1

xQbert
xQbert

Reputation: 35343

This is a pretty basic left join.

Left joins return all records from the table on the LEFT and only those that match from those on the right. So in this example: return all records from master and only those that match in each of the other tables. Thus you get the null values on those records where no matching record could be found in the child tables; yet you still get all records from master.

SELECT M.SKU, RC.*, CC.*, IC.* 
FROM master M
LEFT JOIN resistor_Child RC
 on M.Resistor_ID = RC.Resistor_ID
LEFT JOIN Capacitor_Child CC
 on M.Capacitor_ID = CC.Capacitor_ID
LEFT join Inductor_Child IC
 on M.Inductor_ID = IC.Inductor_ID

Upvotes: 1

Related Questions