Mysql join query

Question

SELECT
    description
FROM 
    diagnosis_mapping 
LEFT JOIN
    diagnosis_codes
ON
    diagnosis_codes.codeid = diagnosis_mapping.codeid

SELECT
    description
FROM 
    diagnosis_mapping 
LEFT JOIN 
    diagnosis_codes
ON
    diagnosis_codes.codeid = diagnosis_mapping.secondarycodeid

How to merge these 2 queries and get info in a single resultset? In first i need to match with codeid and in second i need to match with secondarycodeid to the same mastertable to fetch the description of both.

Answer 1

You can do two joins in one query, just give an alias to the table names so MySQL knows what you want to get:

SELECT
    a.description desc_a,
    b.description desc_b
FROM 
    diagnosis_mapping 
LEFT JOIN
    diagnosis_codes a
ON
    a.codeid = diagnosis_mapping.codeid
LEFT JOIN 
    diagnosis_codes b
ON
    b.codeid = diagnosis_mapping.secondarycodeid

In this example, a is an alias for the first diagnosis_codes table and b to the other. When you give alias names to the tables, MySQL (and any other SQL aware database) treats them basically as two separate tables and fetches data from them independently.

Answer 2

SELECT description FROM (diagnosis_mapping LEFT JOIN diagnosis_codes ON (diagnosis_codes.codeid = diagnosis_mapping.codeid)) LEFT JOIN diagnosis_mapping ON (diagnosis_codes.codeid = diagnosis_mapping.secondarycodeid)

Not sure if thats what you need, but try ;] (might need some spelling checking and adding some "AS" if there are any sql errors)