Help writting Query

[Canman2005]

Hi all

 

Wondering if someone can help  I will try and explain my problem below 

 

Bascially I have 3 tables, they're

 

orders

order_log

owners

 

Firstly the `orders` table holds all the main order info

 

id      status
1       2
2       2
3       3
4       1

 

So what I firstly want to do is a simple

 

SELECT * FROM orders WHERE ((`status` = 1) || (`status` = 2))

 

that would return

 

id      status
1       2
2       2
4       1

 

I want to do that as I want to make sure that I only grab the orders with a status set to 1 or 2

 

I then have a table called `order_log` which stores all the items relating to my orders, it looks like

 

order_id      item_id
1               32
1               40
2               45
3               52
4               45
4               52

 

Now the field `order_id` links to the field `id` in my table `order`, so what I want to do is grab all rows which have a `order_id` returned in the last QUERY, so that would be all rows with an order_id of either "1", "2" or "4", returning me;

 

order_id      item_id
1               32
1               40
2               45
4               45
4               52

 

I then have another table called "product_owners" which holds the data saying which products are owned by which person, so an example is

 

item_id       owner_id
32             1010
40             1020
45             2030
52             2030

 

What I want to do is give a value such as 1020 or 2030 then look at the `item_id` values held by those `owner_id` and then get all rows from the `order_log` table which are linked to that `item_id`.

 

So basically if I fed the system `owner_id` number 2030 then it would grab `item_id` 45 and 52 from the `product_owners` table and then look in the `order_log` table for those `item_id` codes that link them, so in this case with code 2030 it would get

 

order_id      item_id
2               45
4               45
4               52

 

But remembering that it only wants to return rows that have a `status` of 1 or 2 (as seen in the `orders` table.

 

Can anyone help? I did try this but having major issues, I can provide my sad code if you want to see how I am trying to do it.

 

Trying to be as clear as possible

 

Any help would be ace

 

Thanks very much

 

Dave

[flyhoney]

Something to get you started:

SELECT
    *
FROM
    orders,
    order_log,
    owners
WHERE
    orders.status < 3
    AND order_log.order_id = orders.id
    AND owners.order_id = orders.id

[Canman2005]

thanks dude, god knows what I was doing