Conditional query

[CryoGen]

I have a query that does a simple LEFT JOIN to determine whether there's a match in a second table. My problem is that the comparing column on the left side might be different depending on the data in another column.

Here's the skinny: the data are being loaded from a file list on our server. The unique identifier I'm using in my query on the left side might be in column 'level_06' or 'level_07' depending on whether it's category is flagged 'Feature' or 'Department' in the column 'level_05'.

If it's 'Feature', then the FROM clause needs to focus on 'level_06'. If it's a Department, then it need to focus on 'level_07'.

I'm not sure how to construct a query for this, but I used a query I found on MySQL's reference manual and tried to adjust it based on how I know an IF statement works. But it doesn't work here. Any clues on how to rewrite this?

SELECT newline.*, magstat_data.story_slug

IF(

newline.level_05='Feature',

FROM newline LEFT JOIN magstat_data ON newline.level_06 = magstat_data.story_slug,

FROM newline LEFT JOIN magstat_data ON newline.level_07 = magstat_data.story_slug

)
  
WHERE magstat_data.story_slug IS NULL;

Thx.

 

[IanWaterman]

I would just alias the table magstat_data in twice and use a case in your select

Select n.*,
(case when n.level_05='Feature' then m6.datafield else
m7.datafield end ) datafield

from newline n
left outer join magstat_data m6
on n.level_06 = m6.story_slug

left outer join magstat_data m7
on n.level_07 = m7.story_slug

Repeat case as required for any required fields from magstat_data.

Ian

 

[DonQuichote]

You can do a lot in an ON clause, and I would put the magic there. Mind you, if the tables are large the performance is probably lousy. But that is in the database model. So it would be something like:

SELECT *
FROM newline LEFT JOIN magstat_data ON
 (newline.level_06 = magstat_data.story_slug AND newline.category='Feature') OR (magstat_data ON newline.level_07 = magstat_data.story_slug AND newline.category='Department');

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)