CODEWITHSUNDEEP
mysql
Saswat
Saswat

Reputation: 12836

retrieving data from three tables using MySQL

i am having three tables namely

1)cd_register

|----------------------------------------------------|
|   username  |  name   |  age   | sex   | dob       |
|----------------------------------------------------|

2)cd_social

|-------------------------------------|
|   username  |  religion  |  caste   |
|-------------------------------------|

and 3)cd_professional

|----------------------------------------|
|   username  | occupation | education   |
|----------------------------------------|

now i'm using this query in mysql but its giving error

SELECT cd_register.name, cd_register.age, cd_register.dob, cd_social.religion, cd_social.caste, cd_professional.profession
FROM cd_register, cd_social,cd_professional
WHERE 
cd_register.sex = 'Male',
cd_social.religion = 'Hindu',
cd_social.caste = 'Brahmin',
cd_professional.occupation = 'Doctor',
cd_register.username = cd_social.username AND
cd_register.username = cd_professional.username

now i want to mention that username is the primary key of all the tables. Also username of cd_register is foreign key in cd_social. Also username of cd_register is foreign key in cd_professional.

Upvotes: 2

Views: 22694

Answers (3)

safarov
safarov

Reputation: 7804

You can use join

SELECT t1.name, t1.age, t1.dob, t2.religion, t2.caste, t3.profession
FROM cd_register as t1
LEFT JOIN cd_social as t2 
ON t1.username = t2.username and t2.caste = 'Brahmin' and t2.religion ='Hindu'
LEFT JOIN cd_professional as t3 
ON t1.username = t3.username and t3.occupation = 'Doctor' 
WHERE t1.sex = 'Male'

Upvotes: 13

bhamby
bhamby

Reputation: 15450

You have commas in your WHERE clause, which will definitely cause an error (you probably want ANDs there) Try this:

SELECT cdr.name, cdr.age, cdr.dob, cds.religion, cds.caste, cdp.profession
FROM cd_register       cdr
JOIN cd_social         cds
  ON cdr.username    = cds.username
JOIN cd_professional   cdp
  ON cdr.username    = cdp.username
WHERE cdr.sex        = 'Male'
  AND cds.religion   = 'Hindu'
  AND cds.caste      = 'Brahmin'
  AND cdp.occupation = 'Doctor'

Upvotes: 1

jahroy
jahroy

Reputation: 22692

It might help to tell us what the error is.

Also, the last two lines are identical.

You probably need to join the cd_professional table, too.

Upvotes: 0

Related Questions