SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL

Author: Michael James Hernandez, John Viescas
4.0
All Stack Overflow 8

Comments

by anonymous   2017-08-20

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

by anonymous   2017-08-20

You can use the IN predicate, like so:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

SQL Fiddle Demo


it's the opposite version of IN.

No it is not, It is the same as using the ORs the way you did in your question.


To clarify this:

The predicate IN or set membership is defined as1:

enter image description here

Where the Value Expression can be either 2:

enter image description here

So it is fine to do it this way, using the value expression 123, which is a literal.


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

by anonymous   2017-08-20

As @latr0dectus pointed out, you are looking for the EXCEPT. In your example you can achieve this by using NOT IN. The following query will give you:

All the data from Table2 that is not in the above table

SELECT *
FROM Table2
WHERE ITEM_ID NOT IN
(
    SELECT ITEM_ID
    FROM AboveTable
)

Update: Well, if you want to JOIN the two tables any way, then you can do this with LEFT JOIN. Note that you have to add WHERE t1.myCol1 IS NULL in order to get only all the data from Table2 that is not in the above table:

SELECT * 
FROM Table2 t2
LEFT JOIN AboveTable t1 ON t2.ITEM_ID = t1.myCol1
WHERE t1.myCol1 IS NULL

DEMO

Update2: The SQL standard specifies that TableReference1 JOIN TableReference2 ON ... as described by the following diagram1 :

enter image description here

the Table Reference can be either a table name like Table2 directly like in my query above, or a Joined Table, or a SELECT statement that select only some specific columns as you posted in your column.


1 Image From SQL Queries for Mere Mortals