CODEWITHSUNDEEP
mysqlsql
Kirpal Singh
Kirpal Singh

Reputation: 85

Nested sql how to do?

My input is only userid. How do i combine three tables to get area name. I am pretty sure my syntax is correct

TABLE NAME: userinfo userid PRIMARY KEY

TABLE NAME: userinfo_attarea employee_id FOREIGN KEY userid REFERENCE userinfo area_id FOREIGN KEY area_id REFERENCE personnel area

TABLE NAME: personnel area area_id PRIMARY KEY areaname

I tried with this but failed,

SELECT areaname FROM userinfo a
INNER JOIN (SELECT *FROM userinfo_attarea b
INNER JOIN SELECT *FROM personnel_area c
ON b.areaid = c.areaid
) b ON a.userid = b.employee_id;

Upvotes: 0

Views: 52

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Your syntax is NOT correct. You are using parentheses unnecessarily in the FROM clause. You are NOT using parentheses for subqueries.

The subqueries are entirely unnecessary. In addition, you have a poor choice of table aliases (random letters rather than table abbreviations) and you haven't qualified your table names.

SELECT pa.areaname
FROM userinfo ui INNER JOIN
     userinfo_attarea uia
     ON uia.employee_id = ui.userid INNER JOIN
     personnel_area pa
     ON uia.areaid = pa.areaid;

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37430

Your correct syntax would be:

SELECT areaname FROM userinfo a
INNER JOIN (
    SELECT * FROM userinfo_attarea b
    INNER JOIN personnel_area c
    ON b.areaid = c.areaid
) b ON a.userid = b.employee_id;

The SLELECT * FROM on third line is unnecessary.

Also, I strongly recommend proper indentation and correct use of spaces :) (* FROM instead of *FROM). It will increase clarity of your code.

Also, you need to take care of column names, as sometimes they can be ambigious, so you should list them explicitly in inner query and give them unique aliases.

Upvotes: 0

Sandip Chavan
Sandip Chavan

Reputation: 39

select b.areaname from userinfo a INNER JOIN ( SELECT c.areaname FROM ( select * from userinfo_attarea ) b INNER JOIN ( select * from personnel_area ) c on b.areaid = c.areaid ) b on a.userid = b.employee_id;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

i think you this query will help you

select areaname from personel_area pa inner join
userinfo_attarea ut on pa.area_id=ut.area_id
inner join userinfo ui on ut.employee_id=ui.userid

Upvotes: 1

Related Questions