SQL LEFT JOIN ON A SINGLE TABLE

Question

Assume table:

MyTable
idPrimary     idPerson     idSchool
-----------------------------------
1             20            6
2             20            3
3             21            2
4             22            6
5             23            6
6             24            3
7             22            7

I would like to find all the persons, who went to school 6 but did not go to school 2 or 3. That means that from the table above, the answer would be students 22 and 23. Student 20 went to school 6 but sadly went to school 3, thereby negating this student.

According to the matrix in question 38549, I want the LEFT JOIN WHERE B.Key is NULL.

(Just out of curiosity is that what is called LEFT OUTER JOIN?)

The main formula is:

SELECT <SELECT_LIST> FROM TableA.A LEFT JOIN TableB.B ON A.Key = B.Key WHERE B.Key IS NULL;

Table A would be:

SELECT * FROM `MyTable` WHERE `idSchool` = '6';

Table B would be:

SELECT * FROM `MyTable` WHERE `idSchool` = '2' OR `idSchool` = '3';

The resultant table should be:

SELECT `idPerson` FROM SELECT * FROM `MyTable` WHERE `idSchool` = '6' LEFT JOIN SELECT * FROM `MyTable` WHERE `idSchool` = '2' OR `idSchool` = '3' ON `idSchool` = `idSchool` WHERE `idSchool` = NULL;

Sadly MySQL Workbench throws me an error:

Error Code: 1064. 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

Near the LEFT JOIN, so basically the SQL engine does not like my TableA. If I wrap TableA in parenthesis, then I get an alias error.

What is the proper way to issue this query?

What happens if you have somebody who went to three of four schools? — Jaydee, Jun 12 '14 at 15:55
LEFT JOIN is just shorthand for LEFT OUTER JOIN . The main thing required for this is to do a join on the person, with a WHERE clause for the required school and checking for the unwanted school in the ON clause (hence only match on schools that are NOT required). Then check for a field on the table with the unwanted school being NULL. — Kickstart, Jun 12 '14 at 16:12

score 2 · Accepted Answer · answered Jun 12 '14 at 16:09

2

Do a LEFT JOIN, checking for the schools not wanted in the ON clause, and check for no match in the WHERE clause:-

SELECT DISTINCT a.idPrimary, a.idPerson, a.idSchool
FROM MyTable a
LEFT OUTER JOIN MyTable b
ON a.idPerson = b.idPerson
AND b.idSchool IN (2,3)
WHERE a.idSchool = 6
AND b.idSchool IS NULL

answered Jun 12 '14 at 16:09

Kickstart

21,403
2
21
33

Your syntax is quite elegant and clean and shows that I have a long way to go in SQL. At least I was right about that what I want is a left outer join. The Distinct was definitely needed. – Sarah Weinberger Jun 12 '14 at 16:24
1

Most of the time the DISTINCT wouldn't be required (not sure if it is here or not). The important bit is to check the value of b.idSchool in the ON clause. – Kickstart Jun 12 '14 at 16:32

score 0 · Answer 2 · answered Jun 12 '14 at 15:57

0

Use the AS keyword for your table aliases:

SELECT <SELECT_LIST>
  FROM TableA AS A
LEFT JOIN TableB AS B ON A.Key = B.Key
 WHERE B.Key IS NULL;

answered Jun 12 '14 at 15:57

Steven

13,501
27
102
146

score 0 · Answer 3 · answered Jun 12 '14 at 15:59

The syntax of a JOIN looks more like:

SELECT *
FROM someTable
LEFT JOIN ON someOtherTable ON (someTable.someColumn = someOtherTable.someOtherColumn)
WHERE
someTable.id = 1;

However in your case I think NOT EXISTS looks clearer:

SELECT t1.* 
FROM `MyTable` t1
WHERE t1.`idSchool` = '6'
AND NOT EXISTS (
        SELECT t2.idPerson 
        FROM MyTable t2 
        WHERE t2.idSchool IN (2,3) AND t2.idPerson = t1.idPerson);

SQL LEFT JOIN ON A SINGLE TABLE

3 Answers3