Two SQL queries in one sql

Question

I have two queries which I want to combine together in order to get information from two different tables into one array.

 $stmt = $pdo->query("SELECT * FROM Product WHERE id=". $productid);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $itemData = array(
            'id' => $row['id'],
            'name' => $row['name'],
            'unitprice' => $row['unitprice'],
            'quantity' => 1
        );

I would like to add this to the same stmt

SELECT size FROM ProductDetails where id=".$productid);

and then have the $itemData array as follows:

$itemData = array(
                'id' => $row['id'],
                'name' => $row['name'],
                'size' => $row['size'],
                'unitprice' => $row['unitprice'],
                'quantity' => 1
            );

Is there a possible way to do this? Thank you.

`'select p.*, d.size from Product p join ProductDetails d using(id) where id=". $productid'.'";'` — splash58, Feb 21 '18 at 11:24

score 1 · Answer 1 · answered Feb 21 '18 at 11:18

1

You want to use a left join here

The MySQL LEFT JOIN clause allows you to query data from two or more database tables. The LEFT JOIN clause is an optional part of the SELECT statement, which appears after the FROM clause.

So in your example:

SELECT * FROM Product 
LEFT JOIN ProductDetails ON ProductDetails.product_id = Product.product_id
WHERE id=". $productid

answered Feb 21 '18 at 11:18

Kasia Gogolek

3,374
4
33
50

Whats the difference between a LEFT JOIN and an INNER JOIN? – A Garcia Feb 21 '18 at 11:22
INNER JOIN gets all records that are common between both tables based on the foreign key LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL good summary can be found here: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Kasia Gogolek Feb 21 '18 at 11:23
@AGarcia https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – splash58 Feb 21 '18 at 11:27

score 0 · Answer 2 · answered Feb 21 '18 at 11:16

0

Try using a join:

SELECT p.*, pd.size
FROM Product p
INNER JOIN ProductDetails pd
    ON p.id = pd.productid;

answered Feb 21 '18 at 11:16

Tim Biegeleisen

502,043
27
286
360

score 0 · Accepted Answer · answered Feb 21 '18 at 11:32

Use a join in your SQL query and only select the fields you need.

$stmt = $pdo->query("SELECT p.id, p.name, pd.size, p.unitprice FROM Product as p INNER JOIN ProductDetails as pd ON p.id = pd.productid WHERE p.id=". $productid);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $itemData = array(
        'id' => $row['id'],
        'name' => $row['name'],
        'size' => $row['size'],
        'unitprice' => $row['unitprice'],
        'quantity' => 1);

thank you! This has worked ideally as I wanted it to - Great stuff! — A Garcia, Feb 21 '18 at 11:47

Two SQL queries in one sql

3 Answers3