Queries: HELP!

[RichardK]

I have nearly done my database but i need to produce 5 Queries Inner and Outer Joins. But i cant seem to get it to work. Can anyone fix this for me. I understand how it works but i need to fix up mistakes which will help me understand how Queries works.

I want to select the customer's ID, First name and last name and then join that to the customer's Credit Card No.

Customer table is named:
"CUSTOMER_0110020"
Customer_ID
Customer_First_name
Customer_Last_name
Customer_Address_
Customer_Address_2
Customer_Address_3
Customer_Postcode
Customer_Phone

Credit Card Table is Named:
"CREDIT_CARD_0110020"
Customer_ID
Credit_Card_ID
Credit_Card_No
Credit_Card_Type


This is what i have but it's wrong

SELECT Customer_First_name,Customer_Last_name
FROM CUSTOMER_0110020 INNER JOIN CREDIT_CARD_0110020
ON (CUSTOMER_0110020.Customer_ID=CREDIT_CARD_0110020. Credit_Card_No)
WHERE CREDIT_CARD_0110020.Customer_ID

I want it to look like this:

Customer_ID
1110020

Customer_First_name John

Customer_Last_name
Smith

Credit_Card_No
1241-24512-4124

[skhanal]

SELECT CUSTOMER_0110020.Customer_ID,Customer_First_name,C ustomer_Last_name, Credit_Card_No
FROM CUSTOMER_0110020 INNER JOIN CREDIT_CARD_0110020
ON (CUSTOMER_0110020.Customer_ID=CREDIT_CARD_0110020. Customer_ID)

[RichardK]

THANKS!!!

[RichardK]

Just one more question, can you tell me how to join other parts of the tables together?

Example:

Here i have Cust ID, Cust FirstName, Cust LastName, Name, Price and supplier name.


Cust ID, Cust FirstName, Cust LastName
From Customer table

Name, Price and supplier name
From PRODUCT table

Can you tell me how to join them?

SELECT CUSTOMER_0110020. Customer_ID,Customer_First_name,Customer_Last_name , Name, Price, Supplier_Name
FROM CUSTOMER_0110020 LEFT JOIN PRODUCT_0110020
ON (CUSTOMER_0110020.Customer_ID=PRODUCT_0110020.Stoc k_Code)

The Name, Price and Supplier are showing NULL and i cant figure out why. Sorry im a newbie to this

[jkoopmann]

richard,
looks like you are trying to join the customer table "CUSTOMER_0110020" to the product table "PRODUCT_0110020" by using the customer id to the product id. i would think these are two different types of id's and you need an order table of some sort to tell what the customer has ordered. do you have such a table ? try joining that table in with the customer and product tables and let us know.

[RichardK]

No i dont think i have a order table

Im not sure how to do this, These are the tables i have:


CREATE TABLE CUSTOMER_0110020
(Customer_ID INT PRIMARY KEY NOT NULL,
Customer_First_name CHAR (25) NOT NULL,
Customer_Last_name CHAR (25) NOT NULL,
Customer_Address_1 CHAR (35) NOT NULL,
Customer_Address_2 CHAR (35),
Customer_Address_3 CHAR (35),
Customer_Postcode CHAR (10) NOT NULL,
Customer_Phone CHAR (30))


CREATE TABLE CREDIT_CARD_0110020
(Customer_ID INT PRIMARY KEY NOT NULL,
Credit_Card_ID CHAR (25) NOT NULL,
Credit_Card_No CHAR (25) NOT NULL,
Credit_Card_Type CHAR (25) NOT NULL,)




CREATE TABLE PURCHASE_0110020
(Customer_ID INT PRIMARY KEY NOT NULL,
Purchase_ID CHAR (11) NOT NULL,
Order_ID CHAR (11) NOT NULL,
Date CHAR (11) NOT NULL)


CREATE TABLE ORDER_LINE_0110020
(Line_Code INT PRIMARY KEY NOT NULL,
Order_ID CHAR (25) NOT NULL,
Stock_Code CHAR (10) NOT NULL,)



CREATE TABLE PRODUCT_0110020
(Stock_Code INT PRIMARY KEY NOT NULL,
Name CHAR (60) NOT NULL,
Price CHAR (10) NOT NULL,
Supplier_id CHAR (12) NOT NULL,
Supplier_Name CHAR (15) NOT NULL,)

[jkoopmann]

thanks,
you have all the information you need.

1. join CUSTOMER_0110020 to PURCHASE_0110020 through Customer_id
2. join PURCHASE_0110020 to ORDER_LINE_0110020 through Order_id
3. join ORDER_LINE_0110020 to PRODUCT_0110020 through Stock_Code
give it a try

[RichardK]

hmm i understand what you mean but first do i add the entties in each table query? e.g. cust id, name, address etc and so on for the other 2?


SELECT CUSTOMER_0110020. Customer_ID, Customer_First_Name, Customer_Last_Name
FROM CUSTOMER_0110020 INNER JOIN PURCHASE_0110020
ON (CUSTOMER_0110020.Customer_ID=PURCHASE_0110020.Cus tomer_ID)

SELECT PURCHASE_0110020. Order_ID, Date
FROM PURCHASE_0110020 INNER JOIN ORDER_LINE_0110020
ON (PURCHASE_0110020.Order_ID=ORDER_LINE_0110020.Orde r_ID)

SELECT ORDER_LINE_0110020. Stock_Code, Order_ID
FROM ORDER_LINE_0110020 INNER JOIN PRODUCT_0110020
ON (ORDER_LINE_0110020.Stock_Code=PRODUCT_0110020.Sto ck_Code)

[jkoopmann]

my ISO/ANSI standard syntax is that great but it should be something like this.
SELECT CUSTOMER_0110020.Customer_ID,
CUSTOMER_0110020.Customer_First_name,
CUSTOMER_0110020.Customer_Last_name,
PRODUCT_0110020.Name,
PRODUCT_0110020.Price,
PRODUCT_0110020.Supplier_Name
FROM CUSTOMER_0110020 JOIN PURCHASE_0110020 ON CUSTOMER_0110020.Customer_ID = PURCHASE_0110020.Customer_ID
JOIN ORDER_LINE_0110020 ON PURCHASE_0110020.Order_ID = ORDER_LINE_0110020.Order_ID
JOIN PRODUCT_0110020 ON ORDER_LINE_0110020.Stock_Code = PRODUCT_0110020.Stock_Code

[RichardK]

AHHHHHHH THAT'S IT!!!!! BRILL THANKS!!!

[RichardK]

umm i need to create 2 more queries but im stuck on what else to do. I have:

1. Customers and their credit card No
2. Customers and what they bought,price and supplier name
3. Customers what they bought and the date they bought it.

I need 2 more can you suggest what else i should add?