PHP/SQL Statement repeating a query

Question

If I have a list of ID's that I have selected from a statement

SELECT id FROM myTable WHERE name = 'TEST'

This would return just the ids (1001, 1002, 1003, etc...) Then I want to perform another SELECT statement to retrieve all the titles for all those ids.

SELECT title FROM myTable2 WHERE id = XXXX

the id in table2 is the foreign key of table2. id in myTable is the Primary Key. How can I go about retrieving all the titles from those ids. I was thinking about storing all the results of the first select statement in an array, and then using a while loop to iterate through the list and return each result into another array, but my fear is that when the database gets big if it has to return 1000 rows that could be some bad overhead. So in PHP or SQL what is the best way to perform this?

mathematical.coffee · Accepted Answer · 2012-02-14 04:16:59Z

4

You can use a subquery:

SELECT title
FROM myTable2
WHERE id IN (
 SELECT id 
  FROM myTable
  WHERE name = 'TEST'
)

Another way to do it would to be use a JOIN, to avoid the sub-query:

SELECT title
FROM myTable2 
LEFT JOIN myTable
 ON myTable.id = myTable2.id
WHERE myTable.name = 'TEST'

answered Feb 14, 2012 at 4:16

mathematical.coffee

57.1k15 gold badges160 silver badges197 bronze badges

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

4 Comments

zerkms Over a year ago

INNER JOIN better fits the task

mathematical.coffee Over a year ago

But a LEFT JOIN is generally more efficient than the INNER, and the WHERE condition on myTable.name prevents the return of any rows in myTable2 that don't exist in myTable anyway.

zerkms Over a year ago

INNER JOIN is always more efficient than LEFT JOIN if everything else is the same

zerkms Over a year ago

So still don't want to change the answer? ;-)

Hunter McMillen · Accepted Answer · 2012-02-14 04:19:49Z

1

You should just be able to select them at the same time.

SELECT a.id, b.title 
  FROM myTable a, myTable2 b 
  WHERE a.name = 'TEST' AND b.id = a.id;

answered Feb 14, 2012 at 4:19

Hunter McMillen

61.8k25 gold badges124 silver badges176 bronze badges

Comments

JimmyBanks · Accepted Answer · 2012-02-14 04:16:05Z

-1

to select both:

 SELECT id, title FROM mytable WHERE name="TEST"

or to select the whole row

SELECT *  FROM mytable WHERE name="TEST"

if its two tables you are selecting from:

SELECT id, title FROM mytable A JOIN mytable2 B USING (id)

answered Feb 14, 2012 at 4:16

JimmyBanks

4,7989 gold badges48 silver badges74 bronze badges

5 Comments

zerkms Over a year ago

(*) is not a valid sql syntax

zerkms Over a year ago

And your last query probably will throw a error about ambiguously specified column name

JimmyBanks Over a year ago

it was in response to his example, he chose the column name.

Hunter McMillen Over a year ago

@JimmyBanks he meant the column name id you use it twice and don't specify what table it is coming from. SQL will see that there are two possibilities and throw and error.

JimmyBanks Over a year ago

if the id specifies the row of both tables its not necessary to specify what table it is coming from, otherwise a JOIN AS rather than JOIN USING would be necessary. From what he wrote the tables arent auto-incrementing, but the ID is what specifies the rows are correlated together.

Collectives™ on Stack Overflow

PHP/SQL Statement repeating a query

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related