SQL Server 2008中的复杂加入

I have one master table and 5 child table called

我有一个主表和5个子表叫

@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 i.e no data 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:

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

Thanks a lot...

非常感谢...

24456   5392        2190    10  1959 -- Invalid InductorID as not available in child table, so remove from output
24456           8888    29  1959-- Invalid CapacitorID as not available in child table,so remove from output
30583   4233        2190    56  1959-- Invalid Poweramplifierid as not available in child table, remove from output
24455   333333      2190    10  1958-- Invalid AudioAmplifierid as not available in child table, remove from output
696969  7879    1xt 5000    29  2015-- Invalid Poweramplifierid as not available in child table, remove from output
24456   5392        2190    29  1959 -- all valid id and resistorid is null so treated as valid record
24455   4233        2190    56  1959 -- all valid id and resistorid is null so treated as valid record

3 个解决方案

#1

Not sure what is described about blanks but to return rows from master and related details (if any) LEFT JOIN may be applied:

不确定关于空白的描述，但是从主数据和相关细节返回行（如果有的话）可以应用LEFT JOIN：

SELECT *
FROM @MASTER m
LEFT JOIN @POWER_CHILD pc on pc.PowerAmplifierID = m.PowerAmplifierID
LEFT JOIN @AUDIO_CHILD ac on ac.AudioAmplifierID = m.AudioAmplifierID
LEFT JOIN @RESISTOR_CHILD  rc on rc.ResistorID = m.ResistorID
LEFT JOIN @CAPACITOR_CHILD cc on cc.CapacitorID = m.CapacitorID
LEFT JOIN @INDUCTOR_CHILD ic on ic.InductorID = m.InductorID
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)

#2

Heres my sql - I use multiple left joins and coalesce to replace the nulls.

继承我的SQL - 我使用多个左连接和合并来替换空值。

You could add a where clause to remove completely unmatched lines.

您可以添加where子句以删除完全不匹配的行。

select 
m.PowerAmplifierID ,
Coalesce(pc.PowerAmplifier,'') as PowerAmplifier,  

m.AudioAmplifierID , 
Coalesce(ac.AudioAmplifier,'') as AudioAmplifier,   

m.ResistorID  ,
Coalesce(rc.Resistor,'') as Resistor, 

m.CapacitorID ,
Coalesce(cc.Capacitor,'') as Capacitor, 

m.InductorID  ,
Coalesce(ic.InductorID,'') as Inductor, 

m.Years 

from @Master m
left join @Power_Child pc on pc.PowerAmplifierID = m.PowerAmplifierID
left join @Audio_Child ac on ac.AudioAmplifierID = m.AudioAmplifierID
left join @RESISTOR_CHILD rc on rc.ResistorID = m.ResistorID
left join @Capacitor_Child cc on cc.CapacitorID = m.CapacitorID
left join @Inductor_Child ic on ic.InductorID = m.InductorID

To Show only rows which have no invalid Id's in the master table add this where clause

要仅显示主表中没有无效Id的行，请添加此where子句

where 
   not (((m.PowerAmplifierID is not null) and pc.PowerAmplifierID is null)
Or ((m.AudioAmplifierID is not null) and ac.AudioAmplifierID is null)
Or ((m.ResistorID is not null) and rc.ResistorID is null)
Or ((m.CapacitorID is not null) and cc.CapacitorID is null)
Or ((m.InductorID is not null) and ic.InductorID is null))

if you want to show only rows which do have an invalid id in @master use this where clause

如果您只想在@master中显示具有无效ID的行，请使用此where子句

where 
   ((m.PowerAmplifierID is not null) and pc.PowerAmplifierID is null)
Or ((m.AudioAmplifierID is not null) and ac.AudioAmplifierID is null)
Or ((m.ResistorID is not null) and rc.ResistorID is null)
Or ((m.CapacitorID is not null) and cc.CapacitorID is null)
Or ((m.InductorID is not null) and ic.InductorID is null)

In the data set you provided all rows in @master have an invalid id

在您提供的数据集中，@ master中的所有行都具有无效的ID

#3

Try this:

尝试这个：

SELECT m.PowerAmplifierID
    , COALESCE(p.PowerAmplifier, '')
    , a.AudioAmplifierID
    , COALESCE(a.AudioAmplifier, '')
    , c.CapacitorID
    , COALESCE(c.Capacitor, '')
    , i.InductorID
    , COALESCE(i.Inductor, '')
    , r.ResistorID
    , COALESCE(r.Resistor, '')
    , m.Years
FROM @MASTER m
LEFT JOIN @POWER_CHILD p ON m.PowerAmplifierID = p.PowerAmplifierID
LEFT JOIN @AUDIO_CHILD a ON m.AudioAmplifierID = a.AudioAmplifierID
LEFT JOIN @CAPACITOR_CHILD c ON m.CapacitorID = c.CapacitorID
LEFT JOIN @INDUCTOR_CHILD i ON m.InductorID = i.InductorID
LEFT JOIN @RESISTOR_CHILD r ON m.ResistorID = r.ResistorID

COALESCE returns a blank string in this case if the field is NULL.

如果字段为NULL，则COALESCE在这种情况下返回空字符串。

#1