September 28, 2016 at 9:00 am
I have an assignment where I had to create two tables named Customer and Address. These tables are located within a database called HandsOnOne.
Customer has columns titled: CustomerID, CustomerName, CustomerAddressID
Address has columns titled: AddressID, Street, City, State, ZipCode
There is a foreign key relationship in which AddressID in the Address Table is the primary key and CustomerAddressID in the Customer Table is the foreign key.
I used the following code to insert values into each table:
USE HandsOnOne;
INSERT INTO Address (AddressID, Street, City, State, ZipCode)
VALUES (1, '2400 Broadway Drive', 'Missoula', 'MT', '59802'),
(2, '320 21st Street', 'Billings', 'MT', '59101'),
(3, '439 Skyline Blvd', 'Denver', 'CO', '80002'),
(4, '56 Park Avenue', 'New York', 'NY', '10001');
USE HandsOnOne;
INSERT INTO Customer (CustomerID, CustomerName, CustomerAddressID)
VALUES (1, 'Western Supply Company', 1),
(2, 'Nick Harper', 3),
(3, 'Alice Harper', 3),
(4, 'Abacus Consulting', 4);
From there, I have to sort based on certain specifications. The first specification was to list all customers with city and state sorted ascending by ZipCode then ascending by CustomerName.
Here is the code I used for this part:
USE HandsOnOne;
SELECT CustomerName, City, State
FROM Customer, Address
ORDER BY ZipCode ASC, CustomerName ASC;
When I execute this code, my return is 16 items instead of 4. Somehow, each customer is being assigned each address, giving me 4 items at each address.
The next question asks me to list the Street, City, State and ZipCode of all address without a customer associated with them. This query should return the address of 320 21st St Billings, MT 59101 because its AddressID value is 2 and there is no CustomerAddressID value of 2 in the Address table. However, I do not receive any results when I execute this query.
I have verified that there is a foreign key relationship. What am I doing wrong?
September 28, 2016 at 9:09 am
Your problem is right here.
FROM Customer, Address
You have separated your tables with commas using an old style join syntax. Since you don't specify which rows to match in your where clause this is a cross join. You should change this to a proper join.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2016 at 9:13 am
csb5036 (9/28/2016)
USE HandsOnOne;SELECT CustomerName, City, State
FROM Customer, Address
ORDER BY ZipCode ASC, CustomerName ASC
When I execute this code, my return is 16 items instead of 4. Somehow, each customer is being assigned each address, giving me 4 items at each address.
I hope they haven't been teaching you the old-style join syntax. Use the JOIN keyword, and introduce the appropriate predicate with ON. Also, make sure you alias the tables in the join and qualify each column name with a table alias.SELECT
a.col1 AS AColumn1
,a.col2 AS Acolumn2
,b.col1 AS BColumn1
FROM TableA a
JOIN TableB b ON a.ID = b.ID
The next question asks me to list the Street, City, State and ZipCode of all address without a customer associated with them. This query should return the address of 320 21st St Billings, MT 59101 because its AddressID value is 2 and there is no CustomerAddressID value of 2 in the Address table. However, I do not receive any results when I execute this query.
What query are you referring to?
John
September 28, 2016 at 12:48 pm
USE HandsOnOne;
SELECT c.CustomerName, a.City, a.State
FROM Customer c
INNER JOIN Address a
ON a.AddressID = c.CustomerAddressID
ORDER BY a.ZipCode ASC, c.CustomerName ASC;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply