如何加入多个查询

I want to list producers information who produce CD of a particular artist released in a particular year.

My attempt:

$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); 

Here's the error I'm getting:

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

How am I joining the two queries incorrectly?

Try this instead:

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]';

You should put all the referenced tables after the FROM clause with the JOIN ¹:

Where the table reference can be ²:

To put a SELECT or a subquery instead of the table, like this:

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

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

I think you need to do a bit of reading on general SQL and joins.

The SQL you need will be

SELECT 
    *
FROM
    Producer
    INNER JOIN cd
            ON Producer.[name] = cd.[name]

WHERE
    cd.artist = '$_POST[artist]' 
AND cd.title  = '$_POST[title]'

I haven't included table 'song' as I can't see what the join field will be.

I have no idea whether the substitution code for the posted strings is correct: I'm not familiar with PHP. I would strongly recommend that you declare some variables, populate them from $_Post, and pass them into the SQL string with a bit of 'cleaning', as your code looks like an invitation to a SQL injection attack.

Why not?

SELECT * FROM producer as p, cd, song 
WHERE cd.name=p.name AND cd.year= p.year AND 
      cd.title=song.title AND cd.artist = '$_POST[artist]' AND 
      cd.title = '$_POST[title];

Check out the $_POST before query may be interesting