0

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.stack.imgur.com/GzvDs.jpg

Thank you in Advance!

newcommer
  • 29
  • 8
  • A contrived example I just cooked up in my mysql database worked perfectly. Can you provide DDL and some sample data? – Brian A. Henning Sep 01 '17 at 19:25
  • Before asking question, please learn how to ask question. https://stackoverflow.com/help/mcve What's the table structures for files and folders table? – Eric Sep 01 '17 at 19:25
  • 1
    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 Sep 01 '17 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 Sep 01 '17 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 *file*ID). – Thorsten Kettner Sep 01 '17 at 19:28
  • With that I agree. @ThorstenKettner – Jorge Campos Sep 01 '17 at 19:29
  • That's third party database, Let me clear onething that Folders.fileID is varchar. And files.fileid is integer. – newcommer Sep 01 '17 at 19:36
  • In your second query you are selecting from `labtech.agents`. In the subquery of the third query you are selecting from `agents`. Are you sure it is the same table? You might be using two different schemas. – Paul Spiegel Sep 01 '17 at 20:52
  • yes, there is no other table with same name in any database. – newcommer Sep 01 '17 at 20:55

3 Answers3

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))
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
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.

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • Uhm, no. Why would folderID contain values from a field called fileID in another table? – Brian A. Henning Sep 01 '17 at 19:24
  • 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) – Jorge Campos Sep 01 '17 at 19:26
  • @JorgeCampos, Files table doesn't have FolderID. Files has only FileID Integer. But Folders table has folderID (int), fileId (varchar). – newcommer Sep 01 '17 at 19:41
  • 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. – Jorge Campos Sep 01 '17 at 19:44
  • https://www.dropbox.com/sh/f88ziszvp9gani0/AAC6V9rvD2R9Dd0D5XqamEFva?dl=0 – newcommer Sep 01 '17 at 19:52
  • Sorry man, there is no way I would click in a link provided by a stranger over the internet. Export your database as a SQL file and put it in pastebin. It is safer for Us. – Jorge Campos Sep 01 '17 at 20:16
  • Those are screenshots only that I uploaded. Can you guide me how to upload screenshot here on StackOverflow? – newcommer Sep 01 '17 at 20:19
  • Just add it on [imgur](https://imgur.com/), then edit your question with the link. – Jorge Campos Sep 01 '17 at 20:25
  • Ok, I understanded your problem, you have a column with different IDs (889, 10....) each of these values are a DIFFERENT registry. On the second query you return ONE registry that has a value separated by commas '889, 10, ....' so this can not be compared in an IN statement because you can't convert that value on different values it is actually just one string. To do what you are trying you would need to convert that string of GROUPID = `856, 855, etc` into rows. So see this solution: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows .... – Jorge Campos Sep 01 '17 at 20:52
  • My Answer is still valid for your question as it is right now. So either accept it or change your question so it really explain your problem. – Jorge Campos Sep 01 '17 at 20:53
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

nitin.sharma0180
  • 471
  • 3
  • 6
  • I uploaded a folder on dropbox, please look on this. https://www.dropbox.com/sh/f88ziszvp9gani0/AAC6V9rvD2R9Dd0D5XqamEFva?dl=0 – newcommer Sep 01 '17 at 20:05