Using the return value from SQL as an argument in another Query

Question

I am using MySQL and have the following SQL tables

Table1
id | var1 | var2 | var3

Table2
id | var4 | var5

I run the Query

SELECT id, var1 from Table1 where var3 = X

This will return multiple values.

I then want to

SELECT var4 from Table2 where id = Y for each of the ids that have been returned from the first query.

What is the best way to approach this? Can it be done in 1 statement?

EDIT:

To clarify I also need var1 from Table1 to be returned as per the first Query above.

xQbert · Accepted Answer · 2014-06-09 13:13:50Z

1

Lots of ways to accomplish this.

Using an in (as in Linger's reponse)
Using exists
Using a join (as below)

.

SELECT a.id, var4, B.Var1
FROM Table2 A
INNER JOIN Table1 B 
  on A.ID = B.ID
WHERE var3 = X
and ID = Y

Depending on indexes, volume of data table design/setup. Anyone one of these three may be "Best" Exists is generally the fastest, joins solve certain problems if you need additional information.

Read up on Joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

edited Jun 9, 2014 at 13:13

answered Jun 9, 2014 at 13:02

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

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

2 Comments

Ríomhaire Over a year ago

Thanks xQbert. I have tried this and it almost delivers what I need. I have edited my Question for clarity. I also need the results from the first Query to be returned.

Ríomhaire Over a year ago

Perfect Thanks, Accepted. - Slight side note but, when adding additional Joins what does the syntax look like?

Linger · Accepted Answer · 2014-06-09 12:50:16Z

0

How about combining the two queries into one:

SELECT var4 
FROM Table2 
WHERE id IN
(
  SELECT id 
  FROM Table1 
  WHERE var3 = X
)

answered Jun 9, 2014 at 12:50

Linger

15.1k23 gold badges54 silver badges79 bronze badges

3 Comments

Strawberry Over a year ago

I think the OP wanted the 'best way'.

Ríomhaire Over a year ago

Thanks Linger, I'm testing it out now. What would you consider better @Strawberry? Thanks

Strawberry Over a year ago

@Ríomhaire SELECT b.var1,a.var4 FROM table2 a JOIN table1 b ON b.id = a.id WHERE y.var3 = 'x';

Collectives™ on Stack Overflow

Using the return value from SQL as an argument in another Query

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related