Question on the SQL code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Question on the SQL code

Author

Topic

NewToSQLserver
Starting Member

6 Posts

Posted - 2011-01-01 : 07:29:34

On part 6 of SQL 101, in the link: http://www.trainsignaltraining.com/f...raining-videos
1)
we have the code:
select Sales.Customers.Zip 'Zip Code', FlowerResults.CommonName 'Common Name',
case
when FlowerResults.Performance = '1' then
'Star Performer'
when FlowerResults.Performance = '2' then
'Good Performer'
else
'Inconsistant in your climate'
end
'Result'

from Sales.Customers,
(Select Sales.Flowers.CommonName, Sales.ClientFlowerMatch.Performance, ClientFlowerMatch.Zip
from Sales.ClientFlowerMatch, Sales.Flowers
where Sales.ClientFlowerMatch.FlowerID = Sales.Flowers.FlowerID) as FlowerResults
where FlowerResults.Zip = Sales.Customers.Zip and Sales.Customers.CustomerNumber = '10'
order by FlowerResults.Performance, FlowerResults.CommonName

How come the outer Where clause is using Zip to tie the two table, but is not using the CustomerID which is the Primary Key of Customers table to tie the two table Customers and FlowerResults together? I thought we are supposed to use the Primary Key column in order to tie the tables together in the where clause.

2) Also, why select Customers.Zip in not being used but FlowerResults.Zip is being used in the top of the code?
Any help is really appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-01 : 08:07:05

I would run like hell (or abandon) this training series, when they write code that look like this

select		Sales.Customers.Zip 'Zip Code',
		FlowerResults.CommonName 'Common Name',
		case
			when FlowerResults.Performance = '1' then 'Star Performer'
			when FlowerResults.Performance = '2' then 'Good Performer'
			else 'Inconsistant in your climate'
		end 'Result'
from		Sales.Customers,
		(
			Select	Sales.Flowers.CommonName,
				Sales.ClientFlowerMatch.Performance,
				ClientFlowerMatch.Zip
			from	Sales.ClientFlowerMatch,
				Sales.Flowers
			where	Sales.ClientFlowerMatch.FlowerID = Sales.Flowers.FlowerID
		) as FlowerResults
where		FlowerResults.Zip = Sales.Customers.Zip
		and Sales.Customers.CustomerNumber = '10'
order by	FlowerResults.Performance,
		FlowerResults.CommonName

Other than this, the reason behind the code is to check the conditions for all other customers living in the same zip as customer #10.

N 56°04'39.26"
E 12°55'05.63"

NewToSQLserver
Starting Member

6 Posts

Posted - 2011-01-01 : 14:18:09

1) But my questions are: How come the outer Where clause is using Zip to tie the two table, but is not using the CustomerID which is the Primary Key of Customers table to tie the two table Customers and FlowerResults together? I thought we are supposed to use the Primary Key column in order to tie the tables together in the where clause.

2) Also, why select Customers.Zip in not being used but FlowerResults.Zip is being used in the top of the code?
Any help is really appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources