August 11, 2004 at 9:43 am
I've just started at a new company and although I have some experience at sql in some of the counts that we run on our database the following code appears:
select post_2, count(post_2)
from address a
join ( select c.aid,max([newid]) as myid from names c
group by aid) d
on a.aid=d.aid
join names n
on d.myid=n.[newid]
join profiles p
on a.postcode=p.postcode
I get that it then rejoins names to d, but what is d? and what does that code do. Any help would be greatly appreciated as I'm not a big fan of using scripts where I don't know what the mechanics are.
August 11, 2004 at 11:01 am
d is a table alias for a derived table...
Look at it in pseudocode:
Join the address table (a) with the values returned by retrieving (selecting) aid and the maximum newid value from the names table. Give the maximum newid value the column header of 'myid' and alias the names table as c. Group it all by the aid value so I get the maximum newid for each set of aid values. Take all of these values and treat it as a table (known as a derived table)- alias it as d.
-SQLBill
August 11, 2004 at 11:05 am
Forgot to add....I'm not sure myself what the purpose of the joins are. The only columns you are actually retrieving are from the address column, so you shouldn't need any joins. Normally joins are used when you need values from other tables.
For example:
SELECT a.post_2, d.newid, p.postcode
August 12, 2004 at 7:32 am
The joins without columns in the select can be used to filter data as if there was an EXISTS or IN clause. These are not outer joins so they would default to inner joins and cause filtering. It isn't as explicit but it can be more efficient.
August 12, 2004 at 10:12 am
I've found this to be the case. I'd guessed that it was filtering but it doesn't seem to work 100%.
Could you give me a push in the right direction please as to how I would go about making this run alot better.
August 12, 2004 at 11:11 pm
I note that the boys at Microsoft and BOL don't use alias names in their queries. I don't either. It only creates confusion and makes the code more criptic.
Being new to SQL, (post 92 standard) is this a left over from a previous era?
August 17, 2004 at 12:54 pm
If the columns in the select-clause only come from the address table, then I think you can drop the "join names n on ..." clause because no columns are selected from "n" and you know that the rows exist based upon the subquery results.
Personally, I like using the aliases especially when there are lots of joins, and they are required if the same column name in the select-clause appears in more than one table or subquery. I really don't like to see some columns with aliases and some without.
August 17, 2004 at 3:07 pm
I always use aliases. You learn the pretty quick and as long as you are consistent, it's easy for your team to read. If the Products table is always p, you remember it.
August 28, 2004 at 7:16 am
Sorry, a bit late here.
My guess is that this query is telling you how many post_2s exist for each name's latest address.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply