mysql conditional query

Question

I have 2 tables: files and user.

files
----------------------------
|Fileid  | name  | type    |
----------------------------
| 01     | file1 | private |
| 02     | file2 | private |
| 03     | file3 | Public  |
----------------------------

and

user
---------------------
| Fileid  | UserId  |
---------------------
| 01      | user1   | 
| 02      | user1   |
---------------------

How would I select all the record(private and public) for who ever id is in the 'user' table and only 'public' files for any other user where the 'userid' is not in the 'user' table? please help

this question is strangely formatted and confusingly described. — MarianP
– MarianP, Commented Oct 22, 2011 at 18:39

Michael Berkowski · Accepted Answer · 2011-10-22 18:55:38Z

2

If I correctly understand the problem, you are looking for a LEFT JOIN favoring files returning all fields for non-null UserId, or Public only when UserId IS NULL.

SELECT 
  files.*
FROM files LEFT JOIN user ON files.Fileid = user.Fileid
WHERE 
  user.UserId IS NOT NULL
  OR (user.UserId IS NULL AND files.type = 'Public')

edited Oct 22, 2011 at 18:55

answered Oct 22, 2011 at 18:49

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Prasaanth Naidu Over a year ago

i think this is the solution which i was looking for,let me try and see.Thanks @Michael

Marcus Over a year ago

That query would do the trick, except for that you have a typo in your parenthesis; user.User.Id should be user.UserId

Michael Berkowski Over a year ago

@Marcus Just fixed as you were commenting.

Prasaanth Naidu Over a year ago

Hi @Michael ,its not really working ... can it be because of user.UserId IS NOT NULL" ?

Prasaanth Naidu Over a year ago

got it working after changing the field to be null able ,thank you

Ahoura Ghotbi · Accepted Answer · 2011-10-22 18:48:24Z

0

From what I understand you need 2 loops for the first part. one loop will get the user IDs from the table. and the second loop should be placed inside the first one. the second will use the ID in the loop to get all the files.

Second part can be done by having a loop with a sql query like SELECT * FROM files WHERE userID != $userID[$i] and $userID should be an array with all of your users ID.

NOTE : I assumed that you forgot to include a foreign key in the files table to connect both tables together.

answered Oct 22, 2011 at 18:48

Ahoura Ghotbi

2,89615 gold badges40 silver badges65 bronze badges

2 Comments

Prasaanth Naidu Over a year ago

Ahoura Ghotbi,thanks for the reply...is there a way i can do it in the query itself?

Ahoura Ghotbi Over a year ago

Yea if I am not mistaking you can use the union function in SQL and get all the IDs and then compare them with your other table to see if they are there.

Collectives™ on Stack Overflow

mysql conditional query

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related