CODEWITHSUNDEEP
mysql
Elgoots
Elgoots

Reputation: 158

mysql query based on result from one

This will probably be easy for allot of you guru's.

I am trying to get * from two tables.

First table i can match with a user_id. structure is as follows:

table name: order_new

id | service_id | user_id | total_price | total_price_tax | orderstatus | notes | orderdate



table name 2: order_new_items

id | id_order | name | qty | price

The first table ( order_new ) i can get by using the session id. that first table has a row called "id". That id has items in table 2 ( order_new_items ) under the row of "id_order".

Im not sure on how to join the two and pull all data from both tables matching id from first table and id_order from second table

Upvotes: 0

Views: 46

Answers (3)

Refugnic Eternium
Refugnic Eternium

Reputation: 4291

MySQL-Only:

SELECT * FROM order_new o, order_new_items i WHERE user_id = '7' AND o.id_order = i.id;

I'm assuming, that id from order_new is the primary key for the table, while id_order is the foreign key for the 1:n relationship.

To be noted, the 7 is an example of course and needs to be substituted with whatever value you're looking for.

According to comments, I'm answering another question:

$result = mysql_query("SELECT * FROM order_new WHERE user_id = 7");
while ($row = mysql_fetch_array($result)) {
    //store order information
    $res2 = mysql_query("SELECT * FROM order_new_items WHERE id_order = $row[id]");
    while($row2 = mysql_fetch_array($res2)) {
        //store further information
    }
}

Upvotes: 0

fortune
fortune

Reputation: 3372

Try this, this will fetch you data from both tables based on conditon.

SELECT a*, b.id, b.name, b.qty, b.price FROM order_new a 
INNER JOIN order_new_items b on b.id_order = a.id
WHERE a.id = 100

Upvotes: 0

Axel Amthor
Axel Amthor

Reputation: 11096

SELECT * FROM order_new, order_new_items where order_new.id = order_new_items.id and order_new.id = 4711

This will retrieve all rows where an ID exists in bot tables. It will not retrieve rows from table order_new when there are no corresponding rows in order_new_items (i.e. empty order)

To achieve this, you need to use:

 SELECT * FROM order_new
 LEFT JOIN order_new_items on order_new.id = order_new_items.id
 where order_new.id = 4711

probably you need to list columns explicitly instead of *

Upvotes: 1

Related Questions