CODEWITHSUNDEEP
phpmysqlpdo
Spencer TreeHouse
Spencer TreeHouse

Reputation: 35

Multiple select statements in one query

I can do the following in 2 queries but want to make it simpler. Can this be combined in one query? If so how more efficient is it than doing two queries vs one?

query1: SELECT page_id, coupon_id from table_1 WHERE key = :key
query2: SELECT folder from table_2 WHERE page_id = table_1.page_id

For my final result I need to have a coupon_id from table_1, and a folder from table_2.

In query2 I need to use the page_id result from query1 to get the folder

Is there a simpler way to do this?

Upvotes: 1

Views: 518

Answers (5)

bonCodigo
bonCodigo

Reputation: 14361

Try this please:

SELECT a.page_id, a.coupon_id, b.folder_id 
from table_1 a
join table_2 b
ON a.page_id = b.page_id
WHERE a.key = :key
group by a.page_id
;

Upvotes: 0

Jim
Jim

Reputation: 22656

SELECT t1.page_id, t1.coupon_id, t2.folder
FROM table_1 t1 LEFT JOIN table_2 t2 ON (t1.page_id = t2.page_id)
WHERE t1.key = :key

This will be faster than two queries, how much depends on your data.

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65274

SELECT
  table_1.coupon_id AS coupon_id,
  table_2.folder AS folder
FROM
  table_1
  INNER JOIN table_2 ON table_2.page_id = table_1.page_id
WHERE
  table_1.key = :key

Upvotes: 0

Taryn
Taryn

Reputation: 247690

You will want to JOIN the tables on the page_id:

SELECT t1.page_id, 
    t1.coupon_id, 
    t2.folder
from table_1 t1
inner join table_2 t2
    on t1.page_id = t2.page_id
WHERE key = :key

If you need help learning join syntax, here is a great visual explanation of joins.

I used an INNER JOIN which will return all rows that match between the two tables. If you want to return all rows from table_1 even if it doesn't have a matching row in table_2, then you would use a LEFT JOIN

Upvotes: 2

Glavić
Glavić

Reputation: 43552

Use JOIN (LEFT, RIGHT or INNER is up to your needs):

SELECT 
    t1.page_id, 
    t1.coupon_id,
    t2.folder
FROM
    table_1 AS t1
    LEFT JOIN table_2 AS t2 ON
        t2.page_id = t1.page_id
WHERE
    t1.key = :key

Upvotes: 3

Related Questions