Handling Multiple Addresses per Customer in SQL
Handling Multiple Addresses per Customer in SQL
NewCustomers Table:
NewAddresses Table:
Query Explanation:
-
NewCustomers Table:
Stores each customer's unique details likeCustomerID,CustomerName, andEmail. -
NewAddresses Table:
Stores multiple addresses for each customer. Contains:-
AddressType(e.g., Home, Office), -
Street,City,State, andZipCode. -
A
CustomerIDforeign key to link back toNewCustomers.
-
-
Data Insertion:
-
Customer 1 (John Doe) has two addresses: Home and Office.
-
Customer 2 (Jane Smith) has one Home address.
-
-
JOIN Query: Combines both tables using
CustomerIDto show each customer's full address details.
SQL Query:
-- Step 1: Create NewCustomers Table
CREATE TABLE NewCustomers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Email VARCHAR(100)
);
-- Step 2: Create NewAddresses Table
CREATE TABLE NewAddresses (
AddressID INT PRIMARY KEY,
CustomerID INT,
AddressType VARCHAR(50), -- Example: 'Home', 'Office'
Street VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(10),
FOREIGN KEY (CustomerID) REFERENCES NewCustomers(CustomerID)
);
-- Step 3: Insert Data into NewCustomers
INSERT INTO NewCustomers VALUES
(1, 'John Doe', 'john@example.com'),
(2, 'Jane Smith', 'jane@example.com');
-- Step 4: Insert Data into NewAddresses
INSERT INTO NewAddresses VALUES
(101, 1, 'Home', '123 Main St', 'New York', 'NY', '10001'),
(102, 1, 'Office', '456 Business Ave', 'New York', 'NY', '10002'),
(103, 2, 'Home', '789 Park Blvd', 'Los Angeles', 'CA', '90001');
-- Step 5: Retrieve Data using JOIN
SELECT
c.CustomerID,
c.CustomerName,
c.Email,
a.AddressType,
a.Street,
a.City,
a.State,
a.ZipCode
FROM
NewCustomers c
JOIN
NewAddresses a ON c.CustomerID = a.CustomerID;
Output:
