CODEWITHSUNDEEP
mysqlsql
Christoph
Christoph

Reputation: 968

IF/CASE in an outer join

We have two tables that I need to join on where a column (which, at this point, I can't change how/what values are used in that column) aren't using the same value. So depending on the value of the column in the first table, I need to join/select a particular value in the second table. Here is an example (which obviously doesn't work) of what I am trying to do:

SELECT Alert.*, Comments.Comment FROM
Alert
LEFT OUTER JOIN Comments ON Comments.ObjectId = Alert.ObjectId AND
CASE
WHEN Alert.ObjectType = 'U' THEN Comments.ObjectType = 'USER'
WHEN Alert.ObjectType = 'E' THEN Comments.ObjectType = 'EVENT'
END CASE

So I want everything from the Alert table and, if there are corresponding records in the Comments table, I want those, too. But only for the appropriate/matching ObjectType.

I've tried this using both CASE and IF but I can't seem to get it to work. Is something like this possible?

Upvotes: 1

Views: 699

Answers (3)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47048

The CASE statement is made to return a value, not to perform an operation.

Also change the last END CASE to just END.

Use that returned value to compare with in the join condition.

Try:

SELECT Alert.*, Comments.Comment FROM
Alert
LEFT OUTER JOIN Comments ON Comments.ObjectId = Alert.ObjectId AND
Comments.ObjectType = 
CASE Alert.ObjectType
WHEN 'U' THEN 'USER'
WHEN 'E' THEN 'EVENT'
END

Upvotes: 4

therealmitchconnors
therealmitchconnors

Reputation: 2760

Why not just:

SELECT Alert.*, Comments.Comment FROM
Alert
LEFT OUTER JOIN Comments ON Comments.ObjectId = Alert.ObjectId AND
Alert.ObjectType = LEFT(Comments.ObjectType, 1);

It seems a lot simpler...

EDIT

Based on your comment, it seems that not all 'matching' values start with the same letter. In this case, I would recommend designing an intermediate table with columns AlertType char(1) and CommentType varchar(50). Insert each combination of TypeId, like U, User; E, Event; etc. The you can modify your SQL to read

SELECT Alert.*, Comments.Comment FROM
Alert
LEFT OUTER JOIN Intermediate i on Alert.ObjectType = i.AlertType
LEFT OUTER JOIN Comments ON Comments.ObjectId = Alert.ObjectId AND
Comments.ObjectType = i.CommentType;

Upvotes: 0

Dave Collins
Dave Collins

Reputation: 1087

I recommend you handle this with a UNION. In one union join the user comments in another the event comments:

SELECT Alert.*, userComments.Comment
FROM alert
LEFT OUTER JOIN comments usercomments ON userComments.ObjectId = Alert.ObjectId AND usercomments.objecttype='USER'
WHERE alert.objecttype = 'U'
UNION
SELECT Alert.*, eventComments.Comment
FROM alert
LEFT OUTER JOIN comments eventcomments ON eventComments.ObjectId = Alert.ObjectId AND eventcomments.objecttype='EVENT'
WHERE alert.objecttype = 'E'

You don't have to alias them they way I did - it just helps readability.

Upvotes: 0

Related Questions