CODEWITHSUNDEEP
sqlsql-serverjoin
DrakeMurdoch
DrakeMurdoch

Reputation: 859

Join three tables on two columns with blanks where they don't match

I am trying to join three tables on two different columns, with the caveat that I want there to be blanks when certain columns don't match up. The tables hold data on math and English test, so I am joining two test tables based on a common test column with a student table through ID column that they all have in common.

As you will see from the images below, in some cases, the same test contains both math and English, so this appears in both tables, but in other cases, separate tests are written.

The three tables are:

Student Table

English Test Table

Math Test Table

As you can see, the MEG10 test is shared between both English and Math, as they are written together and are in the same test. This is where I am getting tripped up, as I want the join to display all the results when the tests appear in both English and math. When the tests are separate, however, I want it to have blanks in the English or Math columns with "stuff" (because there isn't actually information in those supposed-to-be-blank columns), respectively. Essentially, when I join them, I want the resultant table to be the one shown below.

enter image description here

The code that I am using to join them is:

SELECT 
    m.ID, m.Test, m.M_Col1, m.M_Col2, m.M_Col3,
    e.E_Col1, e.E_Col2, e.E_Col3,
    s.First, s.Last
FROM M_Table AS m
INNER JOIN E_Table AS e ON (m.Test = r.Test)
JOIN S_Table AS s ON (m.ID = s.ID AND e.ID = s.ID)
WHERE m.Test LIKE '%2013%';

This gives me:

Results that I am getting from my JOIN

Now, I understand that I am joining on the Test column, which means that I should only be getting tests that they both share, but I am not sure how to join it such that I get the results that I want. Moreover, I know I am only taking the test column from the math table, but I don't want two test columns.

I am sure there is a way to do this, I am just not sure how. If I need to clarify my question, please let me know.

Upvotes: 1

Views: 1328

Answers (2)

GMB
GMB

Reputation: 222512

You can use FULL OUTER JOIN to bring records from both ends of the relationship between the Math table and the English table. Basically, FULL OUTER JOIN returns all records from both tables, combining them when the join conditions do match, else filling the columns of the missing relation to NULL.

SELECT 
    COALESCE(m.ID, e.ID) id, COALESCE(m.Test, e.Test) Test, 
    m.M_Col1, m.M_Col2, m.M_Col3, 
    e.E_Col1, e.E_Col2, e.E_Col3, 
    s.First, s.Last
FROM M_Table AS m
FULL OUTER JOIN E_Table AS e ON m.Test = e.Test AND e.ID = e.ID
INNER JOIN S_Table AS s ON COALESCE(m.ID, e.ID) =  s.ID
WHERE COALESCE(m.Test, e.Test) LIKE '%2013%';

Tip: merging together tables Math and English would probably simplify your database design. What is the benefit of splitting the information over two tables that have the same structure, and how will it scale when more subjects come into play? You could just use a unique table, with an additional column to store the name of the subject (Math, English, ...).

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You need a list of all the id and tests -- you can get that like this

SELECT m.ID, m.Test FROM Math_Table as M
UNION
SELECT e.ID, e.Test FROM English_Table as E

Run that and understand the output

Then you take that and join it to the names and left join it to the tests like this

SELECT m.ID, m.Test, m.M_Col1, m.M_Col2, m.M_Col3, e.E_Col1, e.E_Col2, e.E_Col3, s.First, s.Last
FROM (
  SELECT m.ID, m.Test FROM Math_Table as M
  UNION
  SELECT e.ID, e.Test FROM English_Table as E
) as base
JOIN S_Table as S ON Base.ID = S.ID
LEFT JOIN M_Table AS m ON m.ID = base.ID AND m.Test = base.Test
LEFT JOIN E_Table AS e ON e.ID = base.ID AND e.Test = base.Test 
WHERE base.Test LIKE '%2013%'

Upvotes: 1

Related Questions