SQL Question about Primary KEY?

Question

I have 3 tables:

CUSTOMERS (First Table)
CustomerID
FirstName
LastName
Address
City
--------------------------------------

ORDERS (Second Table)
OrderID
OrderDateTime
CustomerID
Tax
Shipping


ORDERITEMS Third Table)
OrderItemID
OrderID
ProductID
Quantity

My Question is simple:
How do I refer from The First Table to the Third Table??
For Example How do I JOIN The Customer FirstName with
The Quantity from the Third Table?

From my understanding I need to have a Primary Key & Foreign Key to be able to JOIN 2 table.
I can JOIN the First Table with the Second table as
CustomerID is the JOIn but I can't JOIN from The
Customer First Table to the Third. Please explain with
details if I can?? I am a newbie so please do It with
an easy explanation. Thanks

Answer 1

you will need to join all three tables



select a.firstname, b.orderdatetime, c.productid, c.quantity

from customers a

inner join orders b on a.customerid = b.customerid

inner join orderitems c on b.orderid = c.orderid



in this case you will get all ordered products and their quality for the customer.



tc

Answer 2

SELECT c.FirstName, oi.Quantity

FROM customers c, orders or, orderitems oi

WHERE c.CustomerID = or.CustomerID

AND or.OrderID = oi.OrderID;



You basically use the Orders table as an intermediary even though you do not need to actually display anything from it.



Note that JOIN syntax differs from Oracle to MS-SQL to MySQL, so your command might differ...



Also, Primary Key and Foreign Key are just labels given to columns and the table's columns don't need to be marked as primary or foreign just to do a join. The Primary and Foreign come into play to make sure the tables match up with each other when adding rows.

Answer 3

This is the way I have done that in MS Access:



CUSTOMERS Table

CustomerID (Key)

FirstName

LastName

Address

City



ORDERS Table

CustomerID (Key)

OrderID (Key)

OrderDateTime

Tax

Shipping



ORDERITEMS Table

CustomerID (Key)

OrderID (Key)

ProductID (Key)

UnitPrice

Quantity



Relationship: CUSTOMERS ---> ORDERS ---> ORDERITEMS



Each customer can have multiple orders, and each order can have multiple items

Every order is associated with a customer

Every item is associated with an order



Before any records can be entered into the ORDERS table, the CustomerID MUST be present in the CUSTOMERS table.



Before any records can be entered into the ORDERITEMS table, the CustomerID MUST be present in the CUSTOMERS table AND the OrderID must be present in the ORDERS table.



Note that I have added a UnitPrice field to the ORDERITEMS table -- so the query can show item costs, too.





SELECT CUSTOMERS.CustomerID, ORDERITEMS.OrderID, [FirstName] & " " & [LastName] AS Name, ORDERS.OrderDateTime, ORDERITEMS.ProductID, ORDERITEMS.UnitPrice, ORDERITEMS.Quantity, [UnitPrice]*[Quantity] AS Total FROM (CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CustomerID = ORDERS.CustomerID) INNER JOIN ORDERITEMS ON (ORDERS.OrderID = ORDERITEMS.OrderID) AND (ORDERS.CustomerID = ORDERITEMS.CustomerID) ORDER BY CUSTOMERS.CustomerID, ORDERS.OrderDateTime, ORDERITEMS.ProductID;

Answer 4

SQL Question about Primary KEY?



I have 3 tables:



CUSTOMERS (First Table)

CustomerID

FirstName

LastName

Address

City

------------------------------...



ORDERS (Second Table)

OrderID

OrderDateTime

CustomerID

Tax

Shipping





ORDERITEMS Third Table)

OrderItemID

OrderID

ProductID

Quantity



My Question is simple:

How do I refer from The First Table to the Third Table??

For Example How do I JOIN The Customer FirstName with

The Quantity from the Third Table?



From my understanding I need to have a Primary Key & Foreign Key to be able to JOIN 2 table.

I can JOIN the First Table with the Second table as

CustomerID is the JOIn but I can't JOIN from The

Customer First Table to the Third. Please explain with

details if I can?? I am a newbie so please do It with

an easy explanation. Thanks

Answer 5

You do not need a primary key in table 3 to make it work, but are confusing the issue by having the orderitemid in table 3. You need to use a join from t1 to t2 using the customerid fields, you would be better using the name id in each table as the primary key,:

CUSTOMERS

id int(11) primary key auto_increment,

firstname varchar(...................

lastname varchar(.......... etc



ORDERS

id int (11) primary key auto_increment,

orderdate date .......

customerid int(11),



ITEMS

orderid int(11),

productid int(11),

quantity int(.............



Use a select on t1 and t2 :

$orderres = mysql_query("select firstname,lastname,address,city,orders.id as orderid,tax,shipping from orders left join customers on customerid = customers.id where orders.id = '$orderid'");



This gives the basic headings for an invoice etc.

Then:

$partsres = mysql_query("select productid,quantity from items where orderid = '$orderid'");

$partslist = "PRODUCT QUANTITY ...

//The above should ideally set up a table with the headings in the //first row. Then:

while ($partsrec - mysql_fetch_array($partres){

$partslist .= "$partsrec[productid] $partsrec[quantity]...

//Again set up a table row, you also need to look up the part description from another table by id, this could be another join.

}

This becomes very flexible, it allows for multiple parts, changing any one piece of data (say the product description) causes it to follow through.

If this grows, it may pay to make the customer a clients table, using either company names or the combined firstname,lastname and the address, then make a contacts table for use if you have several contacts at one company. This prevents you being bogged down by changes later. Foreign keys also cause problems with later modifications.

Answer 6

What should be clear to you is that you can do joins without having primary or foreign keys. Primary and foreign keys are simple objects used to enforce constraints on your data.



To accomplish your task you'll need to join the three tables. Primary keys existing or not.

Answer 7

This is the old way of doing things, but it's somewhat easier to understand than the standard JOIN syntax:



SELECT

CUSTOMERS.FirstName,

ORDERITEMS.Quantity



FROM

CUSTOMERS,

ORDERS,

ORDERITEMS



WHERE

CUSTOMERS.CustomerID = ORDERS.CustomerID AND

ORDERS.OrderID = ORDERITEMS.OrderID



Basically, in your FROM clause you specify all the tables, which will create complete mush out of your data (it will combine everything in a row * column fashion). Once the data is all mixed up, you specify how it is related in the WHERE clause (linking keys of one table to those of another), and then choose specific data in the table.column format in your SELECT statement.