MySQL subquery select specific column

Question

I have a subquery that returns 3 columns. I cannot get rid of them. I want to only use one of the columns for the parent query.

For example.

SELECT * FROM table_a WHERE id = (SELECT a,id,b FROM table_b LIMIT 1);

I want to only get id from table_b.

I'm looking for something along the lines of:

SELECT * FROM table_a WHERE id = (SELECT a,id,b FROM table_b LIMIT 1)[id];

How would I accomplish this?

SELECT * FROM table_a WHERE id = (SELECT id FROM table_b LIMIT 1); you cannot use like this? — Joe G Joseph
– Joe G Joseph, Commented Oct 17, 2012 at 6:08
the query is much more complex, the additional columns i cannot get rid of — nick
– nick, Commented Oct 17, 2012 at 6:22

Joe G Joseph · Accepted Answer · 2012-10-17 06:39:04Z

3

how abt this:

SELECT * FROM table_a WHERE id =
(select id from
 (SELECT a,id,b FROM table_b LIMIT 1)a);

answered Oct 17, 2012 at 6:39

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

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

2 Comments

nick Over a year ago

Lol. Yep. Didn't realize it was that easy

Ville Over a year ago

This was a good solution! I was selecting a value with MAX but needed the ID (and so had to select two columns) in a subquery. This solved the problem.

AnandPhadke · Accepted Answer · 2012-10-17 06:00:58Z

0

SELECT * 
FROM table_a 
WHERE id IN
    (
        SELECT a FROM tableB limit 1
        UNION
        SELECT id FROM tableB limit 1
        UNION
        SELECT b FROM tableB limit 1
    )

answered Oct 17, 2012 at 6:00

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Comments

user1744739 · Accepted Answer · 2012-10-17 06:03:47Z

0

SELECT * FROM table_a, (SELECT a,id,b FROM table_b LIMIT 1) temp_b where table_a.id = temp_b.id;

Is this result match your mind?

answered Oct 17, 2012 at 6:03

user1744739

296 bronze badges

Comments

Sashi Kant · Accepted Answer · 2012-10-17 06:10:06Z

0

I think the best way of achieving this is :

SELECT * 
FROM table_a
inner join 
 (
       SELECT a as id FROM tableB
        UNION
        SELECT id FROM tableB
        UNION
        SELECT b as id FROM tableB
) tempB on (table_a.id=tempB.id)

answered Oct 17, 2012 at 6:10

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

MySQL subquery select specific column

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related