The name age score
Peter 26 100
Jack 30 96
Daniel 25 48
Bark 21 69
Jack 27 66
Table B
The name age score
Peter 26 100
Bark 24 69
Jack 27 66
Daniel 25 48
Luke 22 75
(1) table B line data and form A contrast, if age did not appear in table A, belong to the new data, then select out
Such as the above
Bark 24 69
Luke 22 75
(2) while also filter out the name of A field where the B above, score and table at the same time A field name, score the same data
Filter out
Bark 24 69
Finally the results
Luke 22 75
SQL statements should be how to write? thank you
CodePudding user response:
The SELECT temp1. *The FROM (
The SELECT A.n ame, A.a ge, A.s core
FROM A LEFT
The JOIN B ON A.n ame=B.n ame AND A.a ge ge
=B.aWHERE B.n ame IS NULL) AS temp1
LEFT the JOIN B AS temp2 ON temp1. Name=temp2. Name
WHERE temp2. The score IS NOT NULL
CodePudding user response:
The SELECT temp1. *The FROM (
The SELECT A.n ame, A.a ge, A.s core
FROM A RIGHT
The JOIN B ON A.n ame=B.n ame AND A.a ge ge
=B.aWHERE B.n ame IS NULL) AS temp1
LEFT the JOIN B AS temp2 ON temp1. Name=temp2. Name
WHERE temp2. The score IS NOT NULL
Write this to the wrong place the mark red
CodePudding user response:
The SELECT temp1. *The FROM (
SELECT the B.n ame, B.a ge, B.s core
FROM A RIGHT
The JOIN B ON A.n ame=B.n ame AND A.a ge ge
=B.aWHERE B.n ame IS NULL) AS temp1
LEFT the JOIN B AS temp2 ON temp1. Name=temp2. Name
WHERE temp2. The score IS NOT NULL