How to join multiple queries


Problem :

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?



Solution :

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 1:

enter image description here

Where the table reference can be 2:

enter image description here

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


Mysql Tutorials

Mysql Howto..

How can I build MySQL query with additional column equals to number of associated rows?

How to handle huge form [closed]

How to properly use indexing in MySQL

How to save array in MySQL using Servlet?

how to set bulk_insert_buffer_size in mysql?

How to add another column returned for complicated MySQL query

How to prevent apostrophe from being removed PHP MySQL

How to Activate MySQL Database on XAMPP for Windows

How to get Age using BirthDate column by MySQL query?

How to Select MYSQL datetime column to HTML input datetime

How to merge results of two mysql tables in one output

How can I get UTF8 to work correctly using mysql and php?

How to auto populate a name based on first and last name in mysql?

How to display “No results” on search using PHP and MySQL?

How to remove tabs at start and end of varchar field in MySQL?

How to create mysql event inside a procedure or trigger?

How do I use price data in one table for a calculation that is stored in another table?

How to use alphanumeric fields with BETWEEN clause in Mysql?

How do I write SQL to get objects

MySQL SELECT with JOIN - how to code correct

How do I calculate an average date in SQL? [duplicate]

How to define a “unique” constraint on a column of MySQL table in Ruby on Rails 3?

how to save the time that i send data to database inside it?

How to create database with doctrine2?

php how to insert query result from server 1 to server 2 table in mySQL php

How to retrive blob value from database using hibernate?

How can I SUM up all prices according to its sales_order_id (MySQL) [duplicate]

how to get the associated unique ID of the recently added entry in MYSQL

How to wirte a left join query in codeigniter?

How do I make a MySQL query that's equivalent to Fusion Tables “summarize” function?