Page 1 of 1

query

Posted: Tue Sep 20, 2005 3:03 pm
by moiseszaragoza
I need to join all the information of 2 tables into one query

Here are the table names with all the fields so, if you can help me to write a proper query

Table Name
orders

Fields

orders_id, * Primary KEY
customers_id,
customers_name,
customers_company,
customers_street_address,
customers_suburb,
customers_city,
customers_postcode,
customers_state,
customers_country,
customers_telephone,
customers_email_address,
customers_address_format_id,
delivery_name,
delivery_company,
delivery_street_address,
delivery_suburb, delivery_city,
delivery_postcode, delivery_state,
delivery_country, delivery_address_format_id,
billing_name, billing_company,
billing_street_address,
billing_suburb, billing_city,
billing_postcode, billing_state,
billing_country,
billing_address_format_id,
payment_method,
cc_type,
cc_owner,
cc_number,
cc_expires,
last_modified,
date_purchased,
orders_status,
orders_date_finished,
currency,
currency_value,





Table Name
orders_products

Fields
orders_products_id, Primary Key
orders_id, *Link KEY
products_id,
products_model,
products_name,
products_price,
final_price,
products_tax,
products_quantity,

Posted: Tue Sep 20, 2005 3:08 pm
by ryanlwh

Code: Select all

SELECT * FROM orders,orders_products WHERE orders.orders_id=orders_products.orders_id

Posted: Tue Sep 20, 2005 3:25 pm
by John Cartwright
ryanlwh wrote:

Code: Select all

SELECT * FROM orders,orders_products WHERE orders.orders_id=orders_products.orders_id
or

Code: Select all

SELECT * FROM `orders`
INNER JOIN `orders_products` 
USING (`orders_id`)
Both are valid ways of doing it, I just like readability

Posted: Tue Sep 20, 2005 3:38 pm
by moiseszaragoza
would it be to hard to add combine the were clause to say

were orders_status=1

Posted: Tue Sep 20, 2005 3:45 pm
by Jenk
The long method:

Code: Select all

SELECT * FROM orders INNER JOIN orders_products ON orders.orders_id = orders_products.orders_id WHERE orders.orders_status = '1';
:)

Posted: Tue Sep 20, 2005 3:49 pm
by moiseszaragoza
Thanks.
that has been killing me for a month.

I so have to come here more often

Posted: Tue Sep 20, 2005 4:22 pm
by John Cartwright

Code: Select all

SELECT * FROM `orders` AS `t1`
INNER JOIN `orders_products`
USING (`orders_id`) 
WHERE `t1`.`orders_status` =  '1'
:lol: