在JOINed表上MySQL / PDO语法不正确

Question

I am trying to retrieve data from 2 tables using 1 select statement.

Test one fail

$sql = "SELECT banned FROM users WHERE id =:id UNION SELECT first_name, last_name, 
mobile_phone,email, city, address, postal_code, user_type, 
active FROM personal WHERE id =:id";

I have learned from this attempt that you can't have different number of columns selected they need to be even for that to work.

Attempt two fail

$sql = "SELECT users.banned, personal.first_name, personal.last_name, personal.mobile_phone,
personal.email, personal.city, personal.address, personal.postal_code, personal.user_type,
personal.active FROM users WHERE users.id =:id INNER JOIN personal WHERE personal.id =:id";

Got this: SQLSTATE[42000]: Syntax error or access violation: 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 'INNER JOIN personal WHERE personal.id ='21'

Tried selecting by aliases fail

Last attempt fail as well

$sql = "SELECT banned, first_name, last_name, mobile_phone, email, city, address, postal_code, user_type, active FROM users WHERE users.id =:id INNER JOIN personal WHERE personal.id =:id";

Running out of ideas what else to try or what I am doing wrong.

Answer 1

Your syntax and logic are incorrect :

SELECT u.banned
     , p.first_name
     , p.last_name
     , p.mobile_phone
     , p.email
     , p.city
     , p.address
     , p.postal_code
     , p.user_type
     , p.active 
FROM users u JOIN  personal p ON u.id = p.id
WHERE u.id =:id

Some tips:

• when you are using UNION or UNION ALL you should bear in mind, that all SELECTs that are involved should have the same amount of fields.
• WHERE always goes after FROM and JOIN with ON clauses.