我试图从2个mysql表中选择数据到1个查询

问题描述：

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?

我有2个mysql表， p>

第1个表保存有关文件的数据（包括文件夹ID） p>

第二个表保存有关文件夹的数据 p>

我想选择每个用户的文件，我还需要包含该文件夹 name，保存在第二个表中。 p>

所以我需要以某种方式加入这些表。 p>

表1 - 文件 h3> \ n

 `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  ，
  code>  pre> 
 
 表2  - 文件夹 h3> 
 
  `folder_id` int（20）NOT NULL AUTO_INCREMENT，
`  folder_name` varchar（200）NOT NULL，
`u_id` int（11）NOT NULL，
  code>  pre> 
 
 所以我需要能够选择：file_name（table1  ），file_size（table1），folder_name（table2） p> 
 
 
我试了很多次 例如： p> 
 
 
  SELECT files.file_name，files.file_size，folders.folder_name 
FROM files 
 JOIN文件夹ON files.u_id = folders.u_id \  nWHERE 
 files.u_id =？
 AND folders.u_id =？
ORDER BY folders.folder_name，files.file_name 
  code>  pre> 
 
 但这只返回多行 最后每个文件夹名称的文件 p> 
 
 
我有多远？ p> 
  div>

答

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

答

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

答

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

答

 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

答

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

我试图从2个mysql表中选择数据到1个查询

table 1 - files

table 2 - folders

相关推荐