MYSQL - Not getting same result with In select as In with numbers

Question

If I run a query like so:

SELECT DISTINCT fileName
FROM files
WHERE fileID IN (2,3,4,5)

It provides result in 4 rows:

file2

file3

file4

file5

And, the following query provides fileid

SELECT fileID
FROM folders
WHERE folderID = 61

Result is: 2,3,4,5

Can somebody help me find out why I am not getting the same result as 1st query provides?

SELECT DISTINCT fileName
FROM files
WHERE fileID IN (
                    SELECT fileID
                    FROM folders
                    WHERE folderID = 61
                )

Result is: file2

Screenshots: https://i.sstatic.net/GzvDs.jpg

Thank you in Advance!

A contrived example I just cooked up in my mysql database worked perfectly. Can you provide DDL and some sample data? — Brian A. Henning
– Brian A. Henning, Commented Sep 1, 2017 at 19:25
Before asking question, please learn how to ask question. stackoverflow.com/help/mcve What's the table structures for files and folders table? — Eric
– Eric, Commented Sep 1, 2017 at 19:25
Looks fine to me. What about data types; I suppose all IDs are integer? Or is one of them a varchar maybe? A blank (e.g. ' 3') might explain this behaviour. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 1, 2017 at 19:25
People, the problem is with the data. He is comparing the ID of one table with the ID of another table. Although the names seems alike the ids are of course different. — Jorge Campos
– Jorge Campos, Commented Sep 1, 2017 at 19:27
The table structure is a bit weird, by the way. I wouldn't call a table folders, when it's actually files (the records contain a fileID). — Thorsten Kettner
– Thorsten Kettner, Commented Sep 1, 2017 at 19:28

Paul Spiegel · Accepted Answer · 2017-09-01 21:06:03Z

1

Your agents.DriveID column contains a comma delimited string. This is a bad design and WHERE .. IN (subquery) will not work. You will have to use FIND_IN_SET instead. But that will result in poor performance:

SELECT DISTINCT `Name`
FROM MasterGroups
WHERE FIND_IN_SET(GroupID, (SELECT DriveID FROM agents WHERE agents.AgentID = 61076))

answered Sep 1, 2017 at 21:06

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

Comments

Jorge Campos · Accepted Answer · 2017-09-01 19:30:43Z

0

You have a column ID for the table files and you have an ID column for the table folders your problem is simple. You are comparing one ID from one table with the ID of another table.

The correct way would be:

SELECT DISTINCT fileName 
  FROM files 
 WHERE folderID IN -- HERE THE COLUMN YOU SHOULD CHANGE
        (SELECT fileID  FROM folders  WHERE folderID=61)

Where folderID is a column that probably is an FK to folders table. Of course it will depend on your files table structure.

To make sure you are just comparing the wrong columns, just run a select * from ... for both tables, you will see the difference.

edited Sep 1, 2017 at 19:30

answered Sep 1, 2017 at 19:23

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

10 Comments

Brian A. Henning Over a year ago

Uhm, no. Why would folderID contain values from a field called fileID in another table?

Jorge Campos Over a year ago

Because that would be an FK like I said. You see if he has a structure of folders and files the most probable structure would be files (id, filename, folderid); folders(id, foldername)

newcommer Over a year ago

@JorgeCampos, Files table doesn't have FolderID. Files has only FileID Integer. But Folders table has folderID (int), fileId (varchar).

Jorge Campos Over a year ago

Then your problem is just because you are comparing an Integer field with a Varchar field. Like 1 = 'file1' it will never match. And you should review your table structure like this it is bad designed. A better structure would be files (id, name, file_id (fk to this table)) or files (id, name, folder_id (fk to folders)); folders (id, name); You don't need a filename on folders table if you already have a files table.

newcommer Over a year ago

dropbox.com/sh/f88ziszvp9gani0/AAC6V9rvD2R9Dd0D5XqamEFva?dl=0

|

nitin.sharma0180 · Accepted Answer · 2017-09-01 19:35:33Z

0

I replicated the data at my end and getting the expected result.

SELECT DISTINCT fileName FROM files WHERE fileID  IN (2,3,4,5)

Output :- file2 file3 file4 file5

SELECT fileID  FROM folders  WHERE folderID =61

Output: 2 3 4 5

SELECT DISTINCT fileName FROM files WHERE fileID  IN (SELECT fileID  FROM folders  WHERE folderID =61)

OutPut: file2 file3 file4 file5

answered Sep 1, 2017 at 19:35

nitin.sharma0180

4813 silver badges6 bronze badges

1 Comment

newcommer Over a year ago

I uploaded a folder on dropbox, please look on this. dropbox.com/sh/f88ziszvp9gani0/AAC6V9rvD2R9Dd0D5XqamEFva?dl=0

Collectives™ on Stack Overflow

MYSQL - Not getting same result with In select as In with numbers

3 Answers 3

Comments

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related