MySQL join dilemma with inconsistent data

how to prioritize join conditions?

[dhardisty]

Hi Guys,

Imagine two tables of data (in MySQL), each with different kinds of information about one group of students. The tables share two columns -- Student ID and Student Name. The data in these columns is not reliable, so any particular cell may be empty. Furthermore, suppose that the first table has the students' ages, which will be the selection criterion.

What I want to do is write a query that will get data from both tables for all of the 16 year olds. Although the two tables can be joined on the Student ID, sometimes this will be blank, and I want to use Student Name as a fallback.

However, I can't use the following query:
SELECT * FROM table1 JOIN table2 ON (table1.studentID = table2.studentID OR table1.name=table2.name) WHERE age = 16

The reason I can't use the above query is that a few students have the same name. I would like the Student Name join to be used only if the Student ID join fails.

Is there a way to do this?

thanks,
Dave

[FalseDawn]

I would either use a UNION query:
SELECT * FROM table1 T1 JOIN table2 T2 ON (T1.studentID = T2.studentID) WHERE age = 16 AND T1.studentID IS NOT NULL AND T2.studentID IS NOT NULL UNION (SELECT * FROM table1 T1 JOIN table2 T2 ON (T1.name = T2.name) WHERE age = 16 AND (T1.studentID IS NULL OR T2.studentID IS NULL))

(the syntax may not be exactly correct, but it should give you an idea)

Or use a derived column in both tables consisting of the concatenation of the ID and name fields and index and join on that.

[dhardisty]

the UNION should do the trick -- thanks!