I am trying to select data from 2 mysql tables into 1 query

Question

I have 2 mysql tables,

1st table holds data about files (including a folder id)

2nd table holds data about folders

I want to select the files per user, and i also need to include the folder name, which is held in the second table.

So i need to join these tables somehow.

table 1 - files

`file_id` int(20) NOT NULL AUTO_INCREMENT,    
`FILE_NAME` varchar(200) NOT NULL,
`FILE_SIZE` varchar(200) NOT NULL,
`FILE_TYPE` varchar(200) NOT NULL,
`file_added` datetime DEFAULT NULL,
`share_type` varchar(200) NOT NULL,
`folder_id` int(20) NOT NULL,
`u_id` int(11) NOT NULL,

table 2 - folders

`folder_id` int(20) NOT NULL AUTO_INCREMENT,
`folder_name` varchar(200) NOT NULL,
`u_id` int(11) NOT NULL,

So i need to be able to select: file_name(table1), file_size(table1), folder_name(table2)

I've tried a lot of things, like this foe example:

SELECT files.file_name, files.file_size, folders.folder_name 
FROM files
  JOIN folders ON files.u_id = folders.u_id
WHERE 
  files.u_id = ?
  AND folders.u_id = ?
ORDER BY folders.folder_name, files.file_name

but that just returns multiple rows of the files with each folder name at the end

How far wrong am i?

Michael Berkowski · Accepted Answer · 2014-01-09 21:34:13Z

1

The joining column between your two tables relates them via the folder_id, not u_id (which I assume has to do with user ownership).

The WHERE clause in your query may be unnecessary, an artifact of attempting to use an implicit join. For now, remove the WHERE clause entirely and correct the join ON condition. Using WHERE conditions to relate two tables is done when an old-style implicit join is used (comma-separated tables in the FROM clause) but that does not appear to be what you are doing here.

In any case, an explicit JOIN is the modern, preferred syntax.

Add back any WHERE clause you need to limit your results to a filtered set.

SELECT
  files.file_name,
  files.file_size,
  folders.folder_name 
FROM
  files
  JOIN folders ON files.folder_id = folders.folder_id
ORDER BY
  folders.folder_name,
  files.file_name

answered Jan 9, 2014 at 21:34

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

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

Comments

Roger · Accepted Answer · 2014-01-09 21:33:51Z

0

Since its a pretty straight forward query. I would not bother with the JOIN syntax. INNER en OUTER JOINS have there place. But here, a file always has a folder (i assume).

SELECT 
    A.file_name, A.file_size, B.folder_name 
FROM 
    files A, folders B 
WHERE A.folder_id = B.folder_id

A & B are table aliases

answered Jan 9, 2014 at 21:33

Roger

7,6505 gold badges44 silver badges65 bronze badges

Comments

ConstantineUA · Accepted Answer · 2014-01-09 21:35:22Z

0

You should join tables using folder_id field. Try this variant:

SELECT files.file_name, files.file_size, folders.folder_name 
FROM files
  JOIN folders ON files.folder_id = folders.folder_id AND files.u_id = ? ORDER BY folders.folder_name, files.file_name

answered Jan 9, 2014 at 21:35

ConstantineUA

1,0511 gold badge9 silver badges19 bronze badges

Comments

makallio85 · Accepted Answer · 2014-01-09 21:37:08Z

0

Select files.*, folders.folder_name from files join folders on folders.folder_id = files.folder_id where files.u_id = USERID

answered Jan 9, 2014 at 21:37

makallio85

1,3562 gold badges14 silver badges29 bronze badges

Comments

Samira Khorshidi · Accepted Answer · 2014-01-09 21:42:42Z

0

 SELECT files.file_name, files.file_size, folders.folder_name 
    FROM files 
    INNER JOIN folders ON files.u_id = folders.u_id AND files.FOLDER_ID = folders.Folder_ID 
    ORDER BY folders.folder_name, files.file_name

edited Jan 9, 2014 at 21:42

Samira Khorshidi

9301 gold badge9 silver badges30 bronze badges

answered Jan 9, 2014 at 21:36

JB9

498 bronze badges

Collectives™ on Stack Overflow

I am trying to select data from 2 mysql tables into 1 query

table 1 - files

table 2 - folders

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table 1 - files

table 2 - folders

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related