Join two tables and return non-matching rows Mysql-CodePudding

all so l have two tables below, and as you can see in the first table the first row has Accounting as a subject and financial accounting as a topic, in Table two the same data exist but just a bit different.

so the subject column in table one is the same as the subject column in table 2 then the topic column in Table 1 is the same as the name column in table two by the way l mean they have the same data.

so l would like to join these two tables and then only retrieve for example using Accounting as a subject and grade 10, l would like only to get Managerial Accounting & Managing resources as topics and living out Financial Accounting.

so it will be more like non-matching rows Where subject = 'Accounting' AND grade = 'grade 10'

l hope l managed to explain it well there is a result table below using the above where-clause.

Table1

| subject   | topic                | grade   |   email         |
| ----------| ---------------------| --------| ----------------|
| Accounting| Financial Accounting | grade 10| [email protected] |
| Physical S| Chemistry            | grade 12| [email protected] |
| Technology| programming          | grade 11| [email protected]  |

Table2

| name                 | description          | subect    |   grade         |
| -------------------- | ---------------------| --------  | ----------------|
| Financial Accounting | about Accounting     | Accounting| grade 10        |
| Managerial Accounting| about Accounting     | Accounting| grade 10        |
| Chemistry            | about Chemistry      | Physical S| grade 12        |
| Managing resources   | about Managing RSC   | Accounting| grade 10        |
| programming          | about programming    | Technology| grade 11        |

Results

| name                 | description          | subect    |   grade         |
| -------------------- | ---------------------| --------  | ----------------|
| Managerial Accounting| about Accounting     | Accounting| grade 10        |
| Managing resources   | about Managing RSC   | Accounting| grade 10        |

CodePudding user response：

Try the below query:

SELECT 
a.name,
a.description,
a.subject,
a.grade,
b.email
FROM table2 a
LEFT JOIN table1 b
ON a.subject = b.subject
AND a.grade = b.grade
AND a.name = b.topic
GROUP BY 1,2,3,4,5
HAVING b.email IS NULL
;

Hope it helps!

CodePudding user response：

Thanks, @Arihant with your query l managed to find the solution, had to add a few things to make it work, check below.

SELECT 
a.name,
a.subject,
a.grade,
b.email
FROM topic a
LEFT JOIN payment b
ON a.subject = b.subject
AND a.grade = b.grade
AND a.name = b.topic
AND b.email = '[email protected]'
WHERE a.subject = 'Accounting'
AND a.grade = 'Grade 10'
HAVING b.email IS NULL