CODEWITHSUNDEEP
mysqlsql
Rodney Nart
Rodney Nart

Reputation: 121

select records from 3 tables with same column id

I am trying to write an SQL query which will select records of a student within 3 tables that have the same column_I'd.

This is what I wrote but the the records selected are not accurate: 

select 
   Nov_DEC_billing.*, 
   Nov_DEC_students_portfolio.*, 
   admission_form.academic_year 
from 
   Nov_DEC_billing, 
   Nov_DEC_student_portfolio, 
   admission_form 
where 
     Nov_DEC_billing.ID = Nov_DEC_student_portfolio.ID=admission_form.ID 
   AND 
     admission_form.Program ='Nov/dec'

I get a records selected alright but its not accurate. Please what's the right way to join 3 tables that share the same column_id.???

Upvotes: 0

Views: 914

Answers (4)

sticky bit
sticky bit

Reputation: 37472

Nov_DEC_billing.ID=Nov_DEC_student_portfolio.ID=admission_form.ID

doesn't do what you expect. It takes the first part, Nov_DEC_billing.ID=Nov_DEC_student_portfolio.ID and evaluates it. If the values match, that part becomes a 1, if they don't match, it becomes 0. Then the 0 or the 1 is compared against admission_form.ID. That is very likely to give strange results.

So you'd have to split that into:

Nov_DEC_billing.ID=Nov_DEC_student_portfolio.ID
AND Nov_DEC_student_portfolio.ID=admission_form.ID

Or just use explicit join syntax, as the others already advised (and which I do too). That forces you to split this anyway.

Upvotes: 0

Itamar Kerbel
Itamar Kerbel

Reputation: 2568

You should join all the tables to a single one. What you will is join all the tables to a single one and then select from it. Since you have 2 tables you should first join 2 and then join another one on the result. See here left join example for the exact syntax.

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51891

Use JOIN in your query

SELECT b.*, p.*, a.academic_year 
FROM Nov_DEC_billing b
JOIN Nov_DEC_student_portfolio p ON p.id = b.id
JOIN admission_form a ON a.id = b.id
WHERE a.Program='Nov/dec'

Upvotes: 1

Szekelygobe
Szekelygobe

Reputation: 2667

You need to join tables something like this: 

SELECT Nov_DEC_billing.*,
       Nov_DEC_students_portfolio.*,
       admission_form.academic_year 
FROM Nov_DEC_billing AS ndb,
  LEFT JOIN Nov_DEC_student_portfolio AS ndsp ON ndsp.ID=ndb.ID,
  LEFT JOIN admission_form AS af ON af.ID=ndb.ID
WHERE af.Program='Nov/dec'

Upvotes: 0

Related Questions