Select all entry data from two linked (with a relationship) Tables

  • Thread starter Thread starter kikeman
  • Start date Start date
K

kikeman

Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and I
would like to get the "HoursForOrder" at the same time with a single command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.
 
Create a query that joins the two.

SELECT Reports.Id, Reports.OrderNumber, Reports.HoursWorked,
Orders.HoursForOrder
FROM Reports INNER JOIN Orders
ON Reports.OrderNumber = Orders.OrderNumber
 
Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;
 
That won't work, Karl: you'll get a complaint about OrderNumber being
ambiguous, since it exists in both tables and you're not qualifying which
one you want. in the Select clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;

--
Build a little, test a little.


kikeman said:
Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and
I
would like to get the "HoursForOrder" at the same time with a single
command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or
only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.
Click to expand...
Click to expand...
 
Douglas J. Steele said:
That won't work, Karl: you'll get a complaint about OrderNumber being
ambiguous, since it exists in both tables and you're not qualifying which
one you want. in the Select clause.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Try this --
SELECT ID, OrderNumber, HoursForOrder, Sum([HoursWorked]) AS Total_Worked
FROM Orders LEFT JOIN Reports ON Orders.OrderNumber = Reports.OrderNumber
GROUP BY ID, OrderNumber, HoursForOrder;

--
Build a little, test a little.


kikeman said:
Hi,

I have two tables that are liked with a relationship:

"Orders" and "Reports" with the OrderNumber column.

Orders Table (PK -> OrderNumber):
OrderNumber HoursForOrder
123456 47
876433 63

Reports Table (PK -> ID):
ID OrderNumber HoursWorked
4 123456 5
5 123456 4
6 876433 17

Question:

What would be the SQL SELECT command if I have the "ID" of a Report, and
I
would like to get the "HoursForOrder" at the same time with a single
command?

Since they are already linked I am wondering if should I really need to
execute two SELECT command (one SELECT to get the "OrderNumber" from
"Reports" and other SELECT to get the "HoursForOrder" from "Orders ") or
only
one to get all data because the data is linked with a relationship.

Thanks,
Enrique.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top