can someone tell me why

[gjsaturday]

a query would be written this way...

SELECT (SELECT Code
FROM Location AS A
WHERE A.ID = Location.Parent) AS typecode,
(select CODE from type where Location.type=type.ID) as tCode,dbrderRecommendation.Amount, dbrderRecommendation.OrderDate,
dbrderRecommendation.ShipDate
FROM dbrderRecommendation
INNER JOIN dbo.Location ON dbrderRecommendation.ttID = dbo.Location.ID
WHERE DATEDIFF(dd, dbrderRecommendation.OrderDate, GETDATE()) = 0 AND (dbrderRecommendation.OrderType = 0)

when it can easily be accomplished this way...

select type.code, d.code, j.Amount, j.OrderDate, j.ShipDate
from
location t
inner join type d on t.type = d.id
inner join OrderRecommendation j on t.id = j.typetid and j.orderdate = convert(varchar,(getdate()),101)
inner join location type on t.parent = type.id

I don't know who wrote the first one to ask if there was a reason for writing it that way.

Thanks in advance.

 

[r937]

other than an apparent typo (the original uses a join column called ttID) there doesn't seem to be a difference

your condition for the orderdate is more efficient

but as far as the issue of correlated subqueries in the SELECT list versus additional INNER JOINs is concerned, i think they're the same

we've had a couple of threads in this forum about that very difference, and it's quirky as to which is more efficient (only an elaborate testbed would really illustrate this for you)

but semantically, they're the same, right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[iBe]

It looks like the first one was just an ad hoc script thorwn together by someone inexperienced... joins are easier to read.

 

[vongrunt]

I don't know who wrote the first one to ask if there was a reason for writing it that way.

Lemme analyze:

- lotsa dbrderRecommendation, very few table aliases: may be EM query designer
- code alignment style (subqueries, WHERE clause): this screams EM query designer
- existance of correlated subquery: someone skilled with EM query designer (subquery must be manually typed into "Column")
- why subquery and not join: someone has his/her own theories about lotsa joins and performance
- DATEDIFF() criteria: make it work ASAP, tune later... if ever

Overall impression: made by mainstream app developer (VB or something), not DB ninja.

(now dear Watson gimme that pipe ).

Regarding your replacement code, CONVERT() format 112 (ISO) is somewhat better than 101 (US). And date criteria arguably fits better into WHERE clause.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba