3 Replies - 1381 Views - Last Post: 18 April 2008 - 11:58 AM Rate Topic: -----

#1 nelliegirl   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 147
  • Joined: 08-February 08

Queries in Multiple tables

Post icon  Posted 18 April 2008 - 11:26 AM

I need to queries Invoice, Customer and Order_line tables that are not connected to each other. I can connect them going throught Orders table. I am not sure how to connect Order_Line with the queries.

SELECT Invoice.Invoice_Num, Invoice_Date, Customer.Customer_Num, Cust_Name, Amount, Freight_Amount, Invoice_Amount
FROM Invoice, Customer, Order_Line, Orders
WHERE Orders.Customer_Num = Customer.Customer_Num
AND Orders.Invoice_Num =Invoice.Invoice_Num

Is This A Good Question/Topic? 0
  • +

Replies To: Queries in Multiple tables

#2 JasonMcAuley   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 144
  • Joined: 10-April 08

Re: Queries in Multiple tables

Posted 18 April 2008 - 11:43 AM

View Postnelliegirl, on 18 Apr, 2008 - 11:26 AM, said:

I need to queries Invoice, Customer and Order_line tables that are not connected to each other. I can connect them going throught Orders table. I am not sure how to connect Order_Line with the queries.

SELECT Invoice.Invoice_Num, Invoice_Date, Customer.Customer_Num, Cust_Name, Amount, Freight_Amount, Invoice_Amount
FROM Invoice, Customer, Order_Line, Orders
WHERE Orders.Customer_Num = Customer.Customer_Num
AND Orders.Invoice_Num =Invoice.Invoice_Num



Just as a side tip; its generally good practice to alias all your tables/columns when doing selects from multiple tables. Without the alias' I am only making assumptions that the columns in your select are coming from the correct tables.

Another thing, are all your tables One to One relationships ? One to many? Many to Many?

Select  i.Invoice_Num, i.Invoice_Date, c.Customer_Num, c.Cust_Name, o.Amount, o.Freight_Amount, i.Invoice_Amount
From Invoice i
Left Join Orders o On o.Invoice_Num = i.Invoice_Num
Left Join Order_Line ol On ol.Order_Num = o.Order_Num
Left Join Customer c On c.Customer_Num = o.Customer_Num



Essentially; the only thing I have added is link the Order_Line table to a the Orders table. With that link you can now add columns from the Order_Line table to your select statement.

This post has been edited by JasonMcAuley: 18 April 2008 - 11:46 AM

Was This Post Helpful? 0
  • +
  • -

#3 nelliegirl   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 147
  • Joined: 08-February 08

Re: Queries in Multiple tables

Posted 18 April 2008 - 11:53 AM

View PostJasonMcAuley, on 18 Apr, 2008 - 11:43 AM, said:

View Postnelliegirl, on 18 Apr, 2008 - 11:26 AM, said:

I need to queries Invoice, Customer and Order_line tables that are not connected to each other. I can connect them going throught Orders table. I am not sure how to connect Order_Line with the queries.

SELECT Invoice.Invoice_Num, Invoice_Date, Customer.Customer_Num, Cust_Name, Amount, Freight_Amount, Invoice_Amount
FROM Invoice, Customer, Order_Line, Orders
WHERE Orders.Customer_Num = Customer.Customer_Num
AND Orders.Invoice_Num =Invoice.Invoice_Num



Just as a side tip; its generally good practice to alias all your tables/columns when doing selects from multiple tables. Without the alias' I am only making assumptions that the columns in your select are coming from the correct tables.

Another thing, are all your tables One to One relationships ? One to many? Many to Many?

Select  i.Invoice_Num, i.Invoice_Date, c.Customer_Num, c.Cust_Name, o.Amount, o.Freight_Amount, i.Invoice_Amount
From Invoice i
Left Join Orders o On o.Invoice_Num = i.Invoice_Num
Left Join Order_Line ol On ol.Order_Num = o.Order_Num
Left Join Customer c On c.Customer_Num = o.Customer_Num



Essentially; the only thing I have added is link the Order_Line table to a the Orders table. With that link you can now add columns from the Order_Line table to your select statement.


Invoice has columns of Invoice_Num, Invoice_Date, Freight_Amount, Invoice Amount
Customer has columns of Customer_Num, Cust_Name
Order_Line has column of Amount
They connect through Orders
Was This Post Helpful? 0
  • +
  • -

#4 JasonMcAuley   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 144
  • Joined: 10-April 08

Re: Queries in Multiple tables

Posted 18 April 2008 - 11:58 AM

Can you not simply link order_line through orders just as you have done with the other two tables?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1