php - 如何加入多个查询

Question

我想列出制作特定年份发行的特定艺术家 CD 的制作人信息。

我的尝试:

$sql="SELECT * FROM producer, cd WHERE name = name and year= year JOIN SELECT * FROM cd, song WHERE  artist = '$_POST[artist]' AND cd.title ='$_POST[title]'"; 
$data=mysqli_query($sql); 
while ( $row = mysqli_fetch_array($data)) {
print_r($row); 

这是我遇到的错误:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN SELECT * FROM cd, song WHERE artist = '' AND cd.title =''' at line 1

我如何错误地加入两个查询？

Answer 1

试试这个:

SELECT * 
FROM producer AS p 
INNER JOIN cd        ON cd.name  = p.name AND cd.year= p.year 
INNER JOIN song AS s ON cd.title = s.title
WHERE s.artist = '$_POST[artist]' 
  AND cd.title ='$_POST[title]';

您应该将所有引用的表放在 FROM 子句之后，并使用 JOIN ¹:

表格引用可以是²:

要放置SELECT 或子查询而不是表，如下所示:

SELECT * 
FROM producer AS p 
INNER JOIN 
(
    -- here is another select
) AS analias ON -- jon conditino
WHERE ...

---

^{1, 2: 图片来自:SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL}