Another CASE syntax query

[magicmarkuk]

Hi

I am now running this query

SELECT p.topic_id, p.topic_id, p.forum_id, p.author_id + @user_id_jump,
     p.ip_address, p.topic_date,
     (SELECT username FROM members WHERE member_id = p.author_id) AS post_username,
     p.title, p.body,
CASE  
WHEN (SELECT post_id FROM forum_attachments WHERE post.id = p.post_id) != 0 THEN 1     
ELSE 0  
END AS post_attachment,
    FROM forum_topics p;

but receiving the following syntax error

#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 'FROM exp_forum_topics p' at line 14

What I am attempting to do is check if post.id exists in forum_attachments and if it does assign a value of 1 to post_attachment otherwise 0.

Would really appreciate some help and perhaps a recommendation of a good book or online tutorial I could refer to for mysql.

Many thanks
Mark

[rch1231]

Hello,

Based on what you are trying to do I think you will find that there is a simpler way by using a left or right join. If you left join two tables the output will be every record matching your where statement in the first table and the fields from the second table will have a NULL value. For example:

SELECT p.topic_id, 
p.topic_id, 
p.forum_id, 
p.author_id,
p.ip_address, 
p.topic_date,
members.username AS post_username,
p.title, 
p.body,  
forum_attachments.post_id AS has_post_id
FROM forum_topics p
left join members on members.member_id = p.author_id
left join post_id on forum_attachments WHERE forum_attachments.post_id = p.post_id
Having has_post_id is not null

The Having line forces it to only out lines that meet the test even though the select by its self may give more lines. I renamed the field to Has_post_id so there would not be any confusion as to which output field to check for NULL

[magicmarkuk]

Thank you for this.

I must admit to being lost now!

If I simplify the whole original query and remove what was working the problem is with this:

INSERT INTO posts (
post_attachment
)

CASE
WHEN (SELECT post_id FROM forum_attachments WHERE post.id = p.post_id) != 0 THEN 1
ELSE 0
END AS post_attachment,
FROM forum_topics p;

What is is trying to achieve is check whether p.post_id from table forum_topics exists within the forum_attachments table (it may be in once, multiple times or not at all) and if it is then set post_attachement in the posts table to 1 otherwise 0.

I have played around with the code below but can't get it to work but admittedly that is probably because I don't understand fully what it is trying to do.

Many thanks
Mark

Hello,

Based on what you are trying to do I think you will find that there is a simpler way by using a left or right join. If you left join two tables the output will be every record matching your where statement in the first table and the fields from the second table will have a NULL value. For example:

SELECT p.topic_id, 
p.topic_id, 
p.forum_id, 
p.author_id,
p.ip_address, 
p.topic_date,
members.username AS post_username,
p.title, 
p.body,  
forum_attachments.post_id AS has_post_id
FROM forum_topics p
left join members on members.member_id = p.author_id
left join post_id on forum_attachments WHERE forum_attachments.post_id = p.post_id
Having has_post_id is not null

The Having line forces it to only out lines that meet the test even though the select by its self may give more lines. I renamed the field to Has_post_id so there would not be any confusion as to which output field to check for NULL