mysql query based on result from one

Question

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

select * from order_new o join order_new_items i on i.id_order = o.id where service_id = $yourServiceId — juergen d
– juergen d, Commented Aug 23, 2014 at 15:02
@RefugnicEternium: The question is tagged MySQL and OP is asking for a MySQL query. — juergen d
– juergen d, Commented Aug 23, 2014 at 15:03
Thank you very much Refugnic Eternium! I was able to get it working based on your answer. Just chaning the service_id to user_id select * from order_new o join order_new_items i on i.id_order = o.id where user_id = 1111 — Elgoots
– Elgoots, Commented Aug 23, 2014 at 15:07

Axel Amthor · Accepted Answer · 2014-08-23 15:05:05Z

1

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 *

answered Aug 23, 2014 at 15:05

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

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

1 Comment

Elgoots Over a year ago

Refugnic was able to get it working grabbing all data from both tables with his reply. Also thank you for posting your answer too. Appreciate you taking the time to answer.

Refugnic Eternium · Accepted Answer · 2014-08-23 15:46:05Z

0

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
    }
}

edited Aug 23, 2014 at 15:46

answered Aug 23, 2014 at 15:05

Refugnic Eternium

4,3651 gold badge17 silver badges29 bronze badges

9 Comments

Elgoots Over a year ago

i have a new issue :/ Not sure if this can be resolved from the query itself. Because im pulling this data in a php array loop, its displaying the orders 4 times because 4 items exist in the other table.. is there a way to limit certain rows to 1 x in the query?

Refugnic Eternium Over a year ago

@Elgoots First off: Consider accepting an answer, once your actual question is answered. It shows your appreciation the best :)

Refugnic Eternium Over a year ago

@Elgoots Regarding your issue...I'm afraid I don't fully understand the problem. You have four entries in your PHP array and among them is the same order id multiple times?

Elgoots Over a year ago

Sorry it had a time frame on accepting an answer. This image will help with my issue: freweb.com.au/example.jpg So as you can see, if i pull that data from mysql and list it in a table. it repeats those highlited rows. i only wanted those highlighted rows spat out once IF there is more than 1 result in the other table. I hope that makes better sense?

Refugnic Eternium Over a year ago

@Elgoots It does. However I don't think you can do this with one query alone. What you CAN do though is a two-step query. I'll amend my answer (better formatting). Give me a few moments.

|

fortune · Accepted Answer · 2014-08-23 15:11:47Z

0

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

answered Aug 23, 2014 at 15:11

fortune

3,3721 gold badge23 silver badges30 bronze badges

Collectives™ on Stack Overflow

mysql query based on result from one

3 Answers 3

1 Comment

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related