sql1411 (10/1/2012)

---

Hi Ray,
No. The customerID is different for each customer. Below is the revised INSERT statement

GO
INSERT INTO dbo.CustomerPurchase ( PurchaseDate, CustomerID, CustomerName, PurchaseDetails)

SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','Purchased Item 6AB2'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','20%DiscountReceived on Office Furniture'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1002,'John Gray','Purchased $250 worth of Miscellaneous Products'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1003,'Kevin Chang','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1003,'Kevin Chang','Returned OfficeDesk due to defect on the surface'

GO

CELKO (10/1/2012)

---

Most of the work in RDBMS (80 to 95%) is done in the DDL; you have never learned how to design data, so your code looks like a loose leaf note book, full of narratives. Sorry you had only one purchase, that you think the customer’s name is an attribute of the purchase and not the customer. We have DATE data types. We have the ANSI/ISO syntax for insertion now, too.

Tables have keys and should be normalized; you did neither. In short, this is not a valid schema. Here is a guess at doing it right:

CREATE TABLE Customer_Purchase_Comments
(purchase_order_nbr INTEGER NOT NULL,
comment_date DATE NOT NULL,
PRIMARY KEY (purchase_order_nbr INTEGER NOT NULL,
comment_date),
purchase_comment VARCHAR(500) NOT NULL);

CREATE TABLE Purchase_Orders
(purchase_order_nbr INTEGER NOT NULL PRIMARY KEY,
po_date DATE NOT NULL,
customer_id CHAR(10) NOT NULL
REFERENCES Customers(customer_id),
...));

CREATE TABLE Customers
(customer_id CHAR(10) NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..
customer_type CHAR(3) NOT NULL,
CHECK (customer_type IN ('LOY', ..)),
);

>> --Now, from the given records [sic: rows are not records], I want to get only the list of customers who are 'Loyalty Customers' and I want to see all of their purchase details entered for that day. <<

SELECT POC.purchase_order_nbr, POC.purchase_comment,
C.customer_id, C.customer_name
FROM Customer_Purchase_Comments AS POC,
Customers AS C,
Purchase_Orders AS PO
WHERE C.customer_type = 'LOY'
AND POC.comment_date = '2012-06-01'
AND PO.customer_id = C.customer_id;

untested

...
Given that a customer's name can change, perhaps it is an attribute of the purchase.
...