How to write IF ELSE statements in MySQL

Question

I have tables posts and users

basically i want to query if the column privacy of posts table is 0 it means post is public and i want to show this to everyone. but if privacy column is 1,2,3,4 it means this post is only for users with ID 1, 2, 3 and 4

Do you want to say, you've got comma separated values in your privacy column? That would almost never be a good idea. — VMai, Jun 27 '14 at 13:29
can you post sqlfiddle with your database schema and some sample data? — DevelopmentIsMyPassion, Jun 27 '14 at 13:30
yes its comma separated value, what way do you prefer? i m not that good at SQL actually.. — user2765602, Jun 27 '14 at 13:30
@user2765602 I would use a junction table with three columns: `user_id`, `post_id` and `privacy` or so. — VMai, Jun 27 '14 at 13:33
i want the query outcome, posts for person whos id is in PRIVACY column OR privacy column be 0(public) so the person sees it. — user2765602, Jun 27 '14 at 13:44

Reactgular · Answer 1 · 2014-06-27T13:46:57.713

0

This is not a conditional problem it's a sql join through table association problem.

You need to create a join table that associates a post to a user. You can then use a join to select posts based upon their assignment to a user.

The SQL might look something like this

To select posts assigned to user ID = 4

SELECT * FROM `posts` 
    LEFT JOIN `posts_users` ON (`posts`.`id` = `posts_users`.`post_id`) 
    WHERE `posts_users`.`user_id` = 4;

You have also made the assumption that if a post has no users assigned to it, then it's a public post. That requires a check for NULL associations.

SELECT * FROM `posts` 
    LEFT JOIN `posts_users` ON (`posts`.`id` = `posts_users`.`post_id`) 
    WHERE `posts_users`.`user_id` IS NULL;

If you want to find both public and associated posts, then you use a logical OR to match both rules.

SELECT * FROM `posts` 
    LEFT JOIN `posts_users` ON (`posts`.`id` = `posts_users`.`post_id`) 
    WHERE `posts_users`.`user_id` = 4
       OR `posts_users`.`user_id` IS NULL;

edited Jun 27 '14 at 13:46

answered Jun 27 '14 at 13:36

Reactgular

52,335
19
158
208

i want the query outcome, posts for person whos id is in PRIVACY column OR privacy column be 0(public) so the person sees it. – user2765602 Jun 27 '14 at 13:45
`posts_users` is a third data table that defines `post_id` and `user_id` columns. For each user that owns a post there needs to be a record to define that association. – Reactgular Jun 27 '14 at 13:53
http://stackoverflow.com/questions/3029454/sql-query-through-an-intermediate-table – Reactgular Jun 27 '14 at 13:54

How to write IF ELSE statements in MySQL

1 Answers1