Complicated Join in sql server 2008

1

Hello,

i have one master table and 5 child table called as @POWER_CHILD, @AUDIO_CHILD, @RESISTOR_CHILD, @CAPACITOR_CHILD, @INDUCTOR_CHILD

i need to pull data only when there is an match with child table but any column contain blank ('') in master table that will be treated as valid and appear in the output.

i did Inner Join, but no luck. how can i handle blank (''), so i thought to ask to experts.

Kindly don't treat as basic fundamental question. please share your thoughts.

DDL:-

DECLARE @MASTER TABLE
(
PowerAmplifierID VARCHAR (50),
AudioAmplifierID VARCHAR (50),
ResistorID VARCHAR (50),
CapacitorID VARCHAR (50),
InductorID VARCHAR (50),
Years VARCHAR (50)

)

INSERT @MASTER

SELECT '24456', '5392','', '2190', '10', '1959' UNION ALL
SELECT '24456', '', '', '8888', '29', '1959' UNION ALL
SELECT '30583', '4233', '', '2190', '56', '1959' UNION ALL
SELECT '24455', '333333', '','2190','10', '1958' UNION ALL
SELECT '696969', '7879', '1xt','5000','29', '2015' UNION ALL
SELECT '24456', '5392', '', '2190', '29', '1959' UNION ALL
SELECT '24455', '4233', '', '2190', '56', '1959'

DECLARE @POWER_CHILD TABLE

(

PowerAmplifierID VARCHAR (50),

PowerAmplifier VARCHAR (50)

)

INSERT @POWER_CHILD

SELECT '24456', 'Class A Power Amplifiers' UNION ALL
SELECT '24455', 'Class B Power Amplifiers'

DECLARE @AUDIO_CHILD TABLE
(
AudioAmplifierID VARCHAR (50),
AudioAmplifier VARCHAR (50)
)

INSERT @AUDIO_CHILD

SELECT '5392' ,'Transconductance' UNION ALL
SELECT '4233' ,'Transresistance' UNION ALL
SELECT '7879', 'Vacuum-tube'

DECLARE @RESISTOR_CHILD TABLE
(
ResistorID VARCHAR (50),
Resistor VARCHAR (50)
)

INSERT @RESISTOR_CHILD

SELECT '1xt', 'Thick film' UNION ALL
SELECT '2xt', 'Metal film' UNION ALL
SELECT '3xt', 'Wirewound'

DECLARE @CAPACITOR_CHILD TABLE
(
CapacitorID VARCHAR (50),
Capacitor VARCHAR (50)
)

INSERT @CAPACITOR_CHILD

SELECT '2190', 'Film' UNION ALL
SELECT '3536', 'tantalum' UNION ALL
SELECT '9999', 'niobium'

DECLARE @INDUCTOR_CHILD TABLE
(
InductorID VARCHAR (50),
Inductor VARCHAR (50)
)

SELECT '29', 'air core' UNION ALL
SELECT '56', 'parasitic' UNION ALL
SELECT '35', 'Spiderweb'

Expected Output

/*

PowerAmplifierID PowerAmplifier AudioAmplifierID AudioAmplifier ResistorID Resistor CapacitorID Capacitor InductorID Inductor Year

24456 Class A Power Amplifiers 5392 Transconductance '' '' 2190 Film 29 air core 1959
24455 Class B Power Amplifiers 4233 Transresistance '' '' 2190 Film 56 parasitic 1959

2

Hi,

I have an idea about it, it's not very great one , for the moment

SELECT /*DISTINCT*/
    M.PowerAmplifierID AS PowerAmplifierID
    ,PC.PowerAmplifier AS PowerAmplifier
    ,M.AudioAmplifierID  AS AudioAmplifierID
    ,AC.AudioAmplifier AS AudioAmplifier
    ,M.ResistorID AS ResistorID
    ,RC.Resistor AS Resistor
    ,M.CapacitorID AS CapacitorID
    ,CC.Capacitor AS Capacitor
    ,M.InductorID AS InductorID
    ,IC.Inductor AS Inductor
    ,M.Years AS Years
FROM 
    @MASTER AS M
    LEFT JOIN @POWER_CHILD AS  PC
        ON M.PowerAmplifierID = PC.PowerAmplifierID        
    LEFT JOIN @AUDIO_CHILD AS AC
        ON M.AudioAmplifierID = AC.AudioAmplifierID        
    LEFT JOIN @RESISTOR_CHILD AS RC
        ON M.ResistorID = RC.ResistorID        
    LEFT JOIN @CAPACITOR_CHILD AS CC
        ON M.CapacitorID = CC.CapacitorID         
    LEFT JOIN @INDUCTOR_CHILD AS IC
        ON M.InductorID = IC.InductorID        
WHERE
    M.PowerAmplifierID IN (SELECT PowerAmplifierID FROM @POWER_CHILD UNION ALL SELECT '')
    AND M.AudioAmplifierID IN (SELECT AudioAmplifierID FROM @Audio_CHILD UNION ALL SELECT '')
    AND M.ResistorID IN (SELECT ResistorID FROM @RESISTOR_CHILD UNION ALL SELECT '')
    AND M.CapacitorID IN (SELECT CapacitorID FROM @CAPACITOR_CHILD UNION ALL SELECT '')
    AND M.InductorID IN (SELECT InductorID FROM @INDUCTOR_CHILD UNION ALL SELECT '')



PowerAmplifierID  PowerAmplifier           AudioAmplifierID  AudioAmplifier     ResistorID   Resistor    CapacitorID   Capacitor  InductorID                                         Inductor                                           Years
24456             Class A Power Amplifiers 5392              Transconductance   NULL         2190        Film          29         air core                                           1959
24455             Class B Power Amplifiers 4233              Transresistance    NULL         2190        Film          56         parasitic
3

I think, this is more elegant

SELECT 

 M.PowerAmplifierID 
,PC.PowerAmplifier 
,M.AudioAmplifierID 
,AC.AudioAmplifier 
,M.ResistorID 
,RC.Resistor 
,M.CapacitorID 
,CC.Capacitor 
,M.InductorID 
,IC.Inductor 
,M.Years

FROM 
    @MASTER AS M
    CROSS APPLY
        (SELECT PC.*            
         FROM (SELECT PowerAmplifierID , PowerAmplifier FROM @POWER_CHILD UNION ALL SELECT '','') AS  PC
         WHERE PC.PowerAmplifierID = M.PowerAmplifierID ) AS PC
    CROSS APPLY
        (SELECT AC.*
         FROM (SELECT AudioAmplifierID,AudioAmplifier FROM @AUDIO_CHILD  UNION ALL SELECT '','') AS  AC
         WHERE AC.AudioAmplifierID = M.AudioAmplifierID) AS AC
    CROSS APPLY
        (SELECT RC.*
         FROM (SELECT ResistorID,Resistor FROM @RESISTOR_CHILD UNION ALL SELECT '','') AS  RC
         WHERE RC.ResistorID = M.ResistorID) AS RC
    CROSS APPLY
        (SELECT CC.*
         FROM (SELECT CapacitorID,Capacitor FROM @CAPACITOR_CHILD UNION ALL SELECT '','') AS  CC
         WHERE CC.CapacitorID = M.CapacitorID) AS CC
    CROSS APPLY
        (SELECT IC.*
         FROM (SELECT InductorID,Inductor FROM @INDUCTOR_CHILD UNION ALL SELECT '','') AS IC
         WHERE M.InductorID = IC.InductorID) AS IC



PowerAmplifierID PowerAmplifier                 AudioAmplifierID AudioAmplifier       ResistorID Resistor             CapacitorID Capacitor            InductorID Inductor             Years
24456            Class A Power Amplifiers       5392             Transconductance                                     2190        Film                 29         air core             1959
24455            Class B Power Amplifiers       4233             Transresistance                                      2190        Film                 56         parasitic            1959
4

Slight variation on the above from stepson:WHERE (M.PowerAmplifierID = '' or pc.PowerAmplifierID is not null) AND (M.AudioAmplifierID = '' or ac.AudioAmplifierID is not null) AND (M.ResistorID = '' or rc.ResistorID is not null) AND (M.CapacitorID = '' or cc.CapacitorID is not null) AND (M.InductorID = '' or ic.InductorID is not null)I don't like the ORs but I hate, in essence, doing the join all over again in the WHERE clause.

1 Like
5

Good point!

2 Likes
6

Thanks a lot Stepson and Stephen

7

The can be remove by leveraging the properties of NULL assuming CONCAT_NULL_YIELDS_NULL is on/true. The sarg-ability of any indexes may be challenging.

WHERE (M.PowerAmplifierID + pc.PowerAmplifierID >= '')
AND (M.AudioAmplifierID + ac.AudioAmplifierID >= '')
AND (M.ResistorID + rc.ResistorID >= '')
AND (M.CapacitorID + cc.CapacitorID >= '')
AND (M.InductorID + ic.InductorID >= '')

8

Turning it OFF is deprecated, and NOT the default, so sounds like a fair bet to me :slight_smile: