select data from subquery mysql

Question

select t1.table1 from table1 as t1
where t1.column1 
in 
(
    select t2.column2 from table2 as t2 
    join 
    table3 as t3  on t2.column1=t3.column1 
    where t3.columnx=5
);

Above is the mysql query i am firing. Wanted some data from the subquery tables also.

For example say columnxy from table t2.

query that fails

select t1.table1,t2.columnxy from table1 as t1
where t1.column1 
in 
(
    select t2.column2 from table2 as t2 
    join 
    table3 as t3  on t2.column1=t3.column1 
    where t3.columnx=5
);

If i add them with select of the outer query gives error "unknown column" which does make sense.

Is the right way or should rewrite query with joins?

Show the query that failed - we can advise better

BugFinder
– BugFinder

2012-10-16 07:08:26 +00:00
Commented Oct 16, 2012 at 7:08 — BugFinder
– BugFinder, Commented Oct 16, 2012 at 7:08
added the query that is failing.

amitchhajer
– amitchhajer

2012-10-16 07:10:32 +00:00
Commented Oct 16, 2012 at 7:10 — amitchhajer
– amitchhajer, Commented Oct 16, 2012 at 7:10

eggyal · Accepted Answer · 2012-10-16 07:10:58Z

4

Rewrite the query with joins:

SELECT t1.table1, t.columnxy
FROM   table1 AS t1 JOIN (
  SELECT t2.column2, t2.columnxy
  FROM   table2 AS t2 JOIN table3 AS t3 USING (column1)
  WHERE  t3.columnx = 5
) t ON t1.column1 = t.column2

Or:

SELECT t1.table1, t2.columnxy
FROM   table1 AS t1
  JOIN table2 AS t2 ON t1.column1 = t2.column2
  JOIN table3 AS t3 ON t2.column1 = t3.column1
WHERE  t3.columnx = 5

answered Oct 16, 2012 at 7:10

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dan · Accepted Answer · 2012-10-16 07:16:05Z

2

The t2 is not available at that point. You should use a join for this. Using t1.column1=t2.column2 should do it.

edited Oct 16, 2012 at 7:16

answered Oct 16, 2012 at 7:10

dan

13.3k3 gold badges41 silver badges51 bronze badges

1 Comment

amitchhajer Over a year ago

right, that's the issue i am having, searching for the workaround.

Collectives™ on Stack Overflow

select data from subquery mysql

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related