I've got a SQL DB that consists of multiple relational tables. You will find some fields within the master table that reference another table multiple occasions. For instance, say I've got a database of sales staff who're responsible of sales for multiple states. My database has fields for State1, State2, and State3 which map to a States table. I can not determine for that existence of me crafting a question to come back an archive with the enumerated states. Basically only needed one Condition area, I understand I'd do:
SELECT Master.Name, State.Enumeration AS 'State' FROM MasterTable Master, StateTable State WHERE Master.State1 = State.ID;
How do i expand this for those my Condition fields?
Coming back a column from each one of the unique joins towards the states:
select m.Name, s1.Enumeration as State1, s2.Enumeration as State2, s3.Enumeration as State3 from MasterTable m left join StateTable s1 on m.State1 = s1.ID left join StateTable s2 on m.State2 = s2.ID left join StateTable s3 on m.State3 = s3.ID
Coming back 1 column of all of the states from the 3 major joins:
select m.Name, ISNULL(s1.Enumeration + ',','') + ISNULL(s2.Enumeration + ',','') + ISNULL(s3.Enumeration,'') as Enumeration from MasterTable m left join StateTable s1 on m.State1 = s1.ID left join StateTable s2 on m.State2 = s2.ID left join StateTable s3 on m.State3 = s3.ID
There's also column-queries...
select m.Name, ISNULL((select Enumeration from StateTable where ID = m.State1),'') as State1, ISNULL((select Enumeration from StateTable where ID = m.State2),'') as State2, ISNULL((select Enumeration from StateTable where ID = m.State3),'') as State3 from MasterTable m
You should utilize table aliases to be able to join multiple copies of the identical table:
SELECT m.Name, s1.Enumeration AS 'State1', s2.Enumeration AS 'State2' FROM MasterTable m LEFT JOIN StateTable s1 = s1.id = m.state1 LEFT JOIN StateTable s2 = s1.id = m.state2
An INNER JOIN mandates that information is present - otherwise, the whole record is excluded. A LEFT JOIN is safer, like when the state1/2/3/etc enables NULLs...