Select data from only one table using match with another

Question

I want to display all fields in T1 based on a criterial match in T2, but I don't want to display anything from T2.

So when I query

SELECT * FROM Personal 
WHERE Personal.P_ID=Exam.P_ID
AND Exam.Level='B2'
AND Exam.Certification='Full'

I get an error (#1054 - Unknown column 'exam.Level' in 'where clause'), because I don't include the T2 (Exam) in Line 1.

But when I query

SELECT * FROM Personal, Exam 
WHERE Personal.P_ID=Exam.P_ID
AND Exam.Level='B2'
AND Exam.Certification='Full'

I get all the Exam table data returned as well.

How do I just return all the T1 (Personal) data?

I checked for previous questions and found this: Select data (join?) from only one table using an id from another table , but the answers still return the unwanted T2 data.

For example,

SELECT * FROM Personal 
JOIN Exam on Personal.P_ID = Exam.P_ID
AND Exam.Level='B2'
AND Exam.Certification='Full'

You should read join from w3schoools to get to know more about it — Ankit Agrawal, Mar 29 '17 at 13:09

score 2 · Answer 1 · edited Jul 29 '20 at 18:25

2

First of all, don't use comma separated tables in your where clause; that's what join is for (more on this here)

To solve your problem you should change what's in your select

SELECT  Personal.*
FROM    Personal
JOIN    Exam 
ON      Personal.P_ID=Exam.P_ID
WHERE   Exam.Level='B2'
AND Exam.Certification='Full'

This will show all Personal columns, but you can of course select only some columns with

SELECT  Personal.column1, Personal.column2

edited Jul 29 '20 at 18:25

Community

answered Mar 29 '17 at 13:03

Stefano Zanini

What's the exact reason not to use comma separated tables if it gets the same result as a join? – Shogunivar Mar 29 '17 at 13:05
1

Because it makes the query harder to read and more prone to errors. It also makes harder implementing different kinds of join, like left. You can read more about this in the link I added to my answer. – Stefano Zanini Mar 29 '17 at 13:07
Thanks, that's great! Personal.* – Benjamin Mar 29 '17 at 13:15

1 Answers1