How to use IN with WHERE

Databases
1

Hi,

I would like to use IN with WHERE in my MySQL query, I’ve got the following queries the first one gives me the correct result but I don’t get the same with the second query why? many thanks

SELECT DISTINCT archive.document_id,
                        archive.document_status,
                        archive.document_description,
                        files.file_date,
                        files.file_id,
                        files.file_type,
                        buildings.building_name
                   FROM user_join
              LEFT JOIN archive
                     ON archive.document_id = user_join.archive_id
              LEFT JOIN files
                     ON files.file_id = user_join.file_id
			        LEFT JOIN buildings_join
                     ON buildings_join.building_id  = user_join.building_id
              LEFT JOIN buildings
                     ON buildings.building_id = buildings_join.building_id
                  WHERE (user_join.user_id = 34
                    AND archive.document_permission = 1
                     OR archive.document_permission = 4
                     OR archive.document_permission = 7)
                    AND archive.document_status = 1
                    AND buildings_join.user_id = 34
               ORDER BY files.file_date DESC

SELECT DISTINCT archive.document_id,
                        archive.document_status,
                        archive.document_description,
                        files.file_date,
                        files.file_id,
                        files.file_type,
                        buildings.building_name
                   FROM user_join
              LEFT JOIN archive
                     ON archive.document_id = user_join.archive_id
              LEFT JOIN files
                     ON files.file_id = user_join.file_id
			        LEFT JOIN buildings_join
                     ON buildings_join.building_id  = user_join.building_id
              LEFT JOIN buildings
                     ON buildings.building_id = buildings_join.building_id
                     WHERE (user_join.user_id = 34
                    AND archive.document_permission IN (1,4,7)) 
                       AND archive.document_status = 1
                     AND buildings_join.user_id = 34
               
               ORDER BY files.file_date DESC
2

Because priority of ‘AND’ higher than ‘OR’.

In your first query: a AND b OR c OR d.

In second query: a AND (b OR c OR d)

They are not the same.

3

Hi @igor_g thanks for your explanation I didn’t know that AND has higher priority than OR. In this case then is better sto stick with first option? I just tryed to make the query tidier and learn something new at the same time. many thanks

4

It depends, what is result you would to get with your query. They both are not equal. If your first query is correct for you, then use it.

5

Pefect many thanks :slight_smile:

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.