Pulling Data from 2 tables based on an id

[mattyb515]

Ok so I'm looking to pull order information from 2 different tables. From the first table there's a bunch of stuff I need - customer info, address, etc. From the second table all that I need is the products that the person ordered. These are pulled based on the order id number. Below is the query that I'm trying to create but have been unsuccessful so far.

$select = "SELECT orders_id, customers_name, customers_street_address, customers_street_address_2, 
 customers_city, customers_postcode, customers_state, customers_country, customers_telephone, 
 customers_email_address FROM orders WHERE date_purchased BETWEEN now() and dateadd(hh,-24,getdate() 
 JOIN SELECT products_model FROM orders_products WHERE orders.orders_id = orders_products.orders_id)"; 

Any help is greatly appreciated with this one.

[ahmedtheking]

Why not try:

$q1 = "SELECT orders_id, customers_name, customers_street_address, customers_street_address_2,
customers_city, customers_postcode, customers_state, customers_country, customers_telephone,
customers_email_address FROM orders WHERE date_purchased BETWEEN now() and dateadd(hh,-24,getdate())"

$2 = "JOIN SELECT products_model FROM orders_products WHERE orders.orders_id = orders_products.orders_id";

Split them up?

[dreamcatcher]

$select = "SELECT orders.orders_id, orders.customers_name, orders.customers_street_address, orders.customers_street_address_2,
orders.customers_city, customers_postcode, orders.customers_state, orders.customers_country, orders.customers_telephone,
orders.customers_email_address FROM orders
LEFT JOIN orders_products
ON orders.orders_id = orders_products.orders_id
WHERE orders.date_purchased BETWEEN now() and dateadd(hh,-24,getdate())";

Should work ok I think.

dc

[mattyb515]

It does work if I remove everything from the WHERE on. That where statement is throwing everything off. I was able to get it working but it's kind of wordy:

WHERE(
EXTRACT(MONTH FROM a.date_purchased) = EXTRACT(MONTH FROM CURDATE())
AND
DAYOFMONTH(a.date_purchased) = (DAYOFMONTH(CURDATE()) - 1)
)";