how to select post title only when it exists? (need help with if statement)

[rain13]

Hello

 

 

I want to select warn.*, post.PostTitle. The problems is that some rows in warn does not have matching post. In that case I would like to select '' as PostTitle. So I tried a little if statement, but It gives error. Could anyone show me how to correctly write query that selects warn.*, post.PostTitle and that would select constant empty string as PostTitle if there is no post that matches given criteria?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (EXISTS(SELECT 1 FROM post WHERE post.UserID=37)) THEN SELECT warn.*, p' at line 1

IF (EXISTS(SELECT 1 FROM post WHERE post.UserID=37)) THEN    SELECT warn.*, post.PostTitle FROM warn,post WHERE warn.UserID=37 AND  post.UserID=warn.UserIDELSE    SELECT warn.*, "" FROM warn WHERE warn.UserID=37END IF

Here are some cases where post does not exist:1) Administrator or moderator have deleted post after issuing warning for it.2) warning was issued for something else than specific post.In these cases I would like to select '' as PostTitle.

Edited August 7, 2013 by SoItBegins

[justsomeguy]

Use a left join. It will select all records from the left table and return null for fields in the right table that don't have a matching record in the left table.

[rain13]

Thanks for hint. Meanwhile I managed to do it with UNION ALL. Is would LEFT JOIN be any better?Here's my solution incase anyone happens to find this post via search engine or I happen to loose it.

SELECT warn.*, "" AS PostTitle FROM warn WHERE warn.UserID=37 AND (NOT EXISTS (SELECT 1 FROM post WHERE post.UserID=warn.UserID AND post.ID = warn.PostID))UNION ALLSELECT warn.*, post.PostTitle FROM warn,post WHERE warn.UserID=37 AND  post.UserID=warn.UserID AND post.ID = warn.PostID

Edited August 7, 2013 by SoItBegins

[justsomeguy]

What you have has 3 separate select queries including an inner join, a single query and join would be more efficient.

[rain13]

How do I do it with single query and join?

[justsomeguy]

It's this query:

 

SELECT warn.*, post.PostTitle FROM warn,post WHERE warn.UserID=37 AND post.UserID=warn.UserID

 

But with a left join instead of an inner join. Separating tables with a comma like that is an inner join, you want to use a left join instead.

[rain13]

But if I use left join then I still need 2 queries?

 

Is there any chance for me to get an example of it?

Edited August 7, 2013 by SoItBegins

[justsomeguy]

Why? What I pasted above is a single query with a join. All I'm telling you to do is change the type of join.

[rain13]

Edit:

 

Got it. I wasn't quite used to left join. Actually I've never used it with only 1 select

 

So thats' what I've got

SELECT warn.*, post.PostTitle FROM warn LEFT JOIN post ON post.ID = warn.PostID AND post.UserID=warn.UserID WHERE warn.UserID=37

Edited August 7, 2013 by SoItBegins