Sql Server Programming :: Please help with this (simple?) query


	Please help with this (simple?) query Hello, I have what is probably a very simple query to write, but I can't seem to get my head around it. I have a table that hold information about possible delivery methods used by a company. In its simplest form, this table looks like... create table DeliveryMethods ( DeliveryID int not null default 0 primary key, DeliveryMethod varchar(50) not null default '' ) where sample data might be... insert DeliveryMethods (DeliveryID, DeliveryMethod) values (1, 'Royal Mail 2nd Class Recorded') insert DeliveryMethods (DeliveryID, DeliveryMethod) values (2, 'Royal Mail Standard Parcels') I then have another table that holds the delivery rates by weight for these methods... create table DeliveryRates ( DeliveryID int foreign key references DeliveryMethods(DeliveryID), Weight int not null default 0, Rate float not null default 0.0 ) where sample data might look like... insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 100, 1.62) insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 250, 1.90) insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 500, 2.22) insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 750, 2.62) insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1000, 3.85) insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1500, 4.95) insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 2000, 5.68) insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 4000, 8.24) Obviously the real data would be a lot more than this, but this gives you the idea. I have a couple of tasks here... 1) to be able to pull out the delivery rate for each method, given a specific weight. For example, if the weight were 300g, then I would want to get the following data returned... DeliveryID, Rate 1, 2.22 2, 3.85 Which would tell me that delivery by 2nd class recorded would be $2.22 and delivery by standard parcels would be $3.85. My current attempt at this is the following... select dm.deliveryid, dr.Rate from DeliveryMethods as dm inner join DeliveryRates as dr On dr.DeliveryID=dm.DeliveryID Where dr.Weight>0 and dr.Rate>0 And @weight<=dr.Weight and Rate = (select top 1 Rate from DeliveryRates as dr2 where @weight<=dr2.Weight and DeliveryID=dm.DeliveryID order by Rate asc) order by dm.DeliveryID This seems to work, but looks inelegant to me. Can anyone suggest a neater and/or more efficient way of doing it? 2) the other task is similar, but requires me to return just the single lowest delivery rate, instead of one rate for each method. I'm stuck on this one. Anyone able to help? Thanks a lot. -- Alan Silver (anything added below this line is nothing to do with me) Solution to 1: select a.deliveryID, a.rate from deliveryrates a inner join ( select deliveryID, min(weight) w from deliveryrates where weight >= 1000 group by deliveryID) b on a.deliveryid = b.deliveryid and a.weight = b.w On May 28, 12:04 pm, Alan Silver <alan-sil@nospam.thanx.invalid> wrote: - Hide quoted text - > Hello, > I have what is probably a very simple query to write, but I can't seem > to get my head around it. > I have a table that hold information about possible delivery methods > used by a company. In its simplest form, this table looks like... > create table DeliveryMethods ( > DeliveryID int not null default 0 primary key, > DeliveryMethod varchar(50) not null default '' > ) > where sample data might be... > insert DeliveryMethods (DeliveryID, DeliveryMethod) > values (1, 'Royal Mail 2nd Class Recorded') > insert DeliveryMethods (DeliveryID, DeliveryMethod) > values (2, 'Royal Mail Standard Parcels') > I then have another table that holds the delivery rates by weight for > these methods... > create table DeliveryRates ( > DeliveryID int foreign key references DeliveryMethods(DeliveryID), > Weight int not null default 0, > Rate float not null default 0.0 > ) > where sample data might look like... > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 100, 1.62) > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 250, 1.90) > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 500, 2.22) > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 750, 2.62) > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1000, 3.85) > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1500, 4.95) > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 2000, 5.68) > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 4000, 8.24) > Obviously the real data would be a lot more than this, but this gives > you the idea. > I have a couple of tasks here... > 1) to be able to pull out the delivery rate for each method, given a > specific weight. For example, if the weight were 300g, then I would want > to get the following data returned... > DeliveryID, Rate > 1, 2.22 > 2, 3.85 > Which would tell me that delivery by 2nd class recorded would be $2.22 > and delivery by standard parcels would be $3.85. My current attempt at > this is the following... > select dm.deliveryid, dr.Rate > from DeliveryMethods as dm > inner join DeliveryRates as dr > On dr.DeliveryID=dm.DeliveryID > Where dr.Weight>0 and dr.Rate>0 > And @weight<=dr.Weight > and Rate = (select top 1 Rate from DeliveryRates as dr2 where > @weight<=dr2.Weight and DeliveryID=dm.DeliveryID order by Rate asc) > order by dm.DeliveryID > This seems to work, but looks inelegant to me. Can anyone suggest a > neater and/or more efficient way of doing it? > 2) the other task is similar, but requires me to return just the single > lowest delivery rate, instead of one rate for each method. I'm stuck on > this one. > Anyone able to help? Thanks a lot. > -- > Alan Silver > (anything added below this line is nothing to do with me) Answer to #2: select top 1 deliveryID, rate from deliveryrates where weight >= 300 order by rate On May 28, 12:32 pm, Jason Lepack <jlep@gmail.com> wrote: - Hide quoted text - > Solution to 1: > select > a.deliveryID, > a.rate > from > deliveryrates a > inner join > ( > select > deliveryID, > min(weight) w > from > deliveryrates > where > weight >= 1000 > group by > deliveryID) b > on a.deliveryid = b.deliveryid > and a.weight = b.w > On May 28, 12:04 pm, Alan Silver <alan-sil@nospam.thanx.invalid> > wrote: > > Hello, > > I have what is probably a very simple query to write, but I can't seem > > to get my head around it. > > I have a table that hold information about possible delivery methods > > used by a company. In its simplest form, this table looks like... > > create table DeliveryMethods ( > > DeliveryID int not null default 0 primary key, > > DeliveryMethod varchar(50) not null default '' > > ) > > where sample data might be... > > insert DeliveryMethods (DeliveryID, DeliveryMethod) > > values (1, 'Royal Mail 2nd Class Recorded') > > insert DeliveryMethods (DeliveryID, DeliveryMethod) > > values (2, 'Royal Mail Standard Parcels') > > I then have another table that holds the delivery rates by weight for > > these methods... > > create table DeliveryRates ( > > DeliveryID int foreign key references DeliveryMethods(DeliveryID), > > Weight int not null default 0, > > Rate float not null default 0.0 > > ) > > where sample data might look like... > > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 100, 1.62) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 250, 1.90) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 500, 2.22) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (1, 750, 2.62) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1000, 3.85) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 1500, 4.95) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 2000, 5.68) > > insert DeliveryRates (DeliveryID, Weight, Rate) values (2, 4000, 8.24) > > Obviously the real data would be a lot more than this, but this gives > > you the idea. > > I have a couple of tasks here... > > 1) to be able to pull out the delivery rate for each method, given a > > specific weight. For example, if the weight were 300g, then I would want > > to get the following data returned... > > DeliveryID, Rate > > 1, 2.22 > > 2, 3.85 > > Which would tell me that delivery by 2nd class recorded would be $2.22 > > and delivery by standard parcels would be $3.85. My current attempt at > > this is the following... > > select dm.deliveryid, dr.Rate > > from DeliveryMethods as dm > > inner join DeliveryRates as dr > > On dr.DeliveryID=dm.DeliveryID > > Where dr.Weight>0 and dr.Rate>0 > > And @weight<=dr.Weight > > and Rate = (select top 1 Rate from DeliveryRates as dr2 where > > @weight<=dr2.Weight and DeliveryID=dm.DeliveryID order by Rate asc) > > order by dm.DeliveryID > > This seems to work, but looks inelegant to me. Can anyone suggest a > > neater and/or more efficient way of doing it? > > 2) the other task is similar, but requires me to return just the single > > lowest delivery rate, instead of one rate for each method. I'm stuck on > > this one. > > Anyone able to help? Thanks a lot. > > -- > > Alan Silver > > (anything added below this line is nothing to do with me)- Hide quoted text - > 1) Do NOT use floating point numbers for currency; stick to DECIMAL(s,p). Lot less work AND no rounding errors. 2) Instead of thinking in codes, think in data. I changed your names a bit to align them with ISO-11179 3) While I applaud DEFAULTs, they are NOT a good idea in your CASE. 4) You might consider using an abbreviation code for the delivery methods so they would be easier for a human being to use. 5) Minor points; you are using needlessly proprietary T-SQL; it is INSERT INTO, you use ORDER BY in subqueries AND put your DEFAULTs in the wrong order. CREATE TABLE DeliveryMethods (delivery_code INTEGER NOT NULL PRIMARY KEY, delivery_description VARCHAR(50) NOT NULL); INSERT INTO DeliveryMethods VALUES (1, 'Royal Mail 2nd Class Recorded'); INSERT INTO DeliveryMethods VALUES (2, 'Royal Mail Standard Parcels'); The real trick is to put in a weight range in your rates table: CREATE TABLE DeliveryRates (delivery_code INTEGER NOT NULL REFERENCES DeliveryMethods(delivery_code), low_parcel_wgt INTEGER NOT NULL, high_parcel_wgt INTEGER NOT NULL, CHECK (low_parcel_wgt < high_parcel_wgt), delivery_rate DECIMAL(6,2) NOT NULL, PRIMARY KEY (delivery_code, low_parcel_wgt)); INSERT INTO DeliveryRates VALUES (1, 0, 100, 1.62); INSERT INTO DeliveryRates VALUES (1, 101, 250, 1.90); INSERT INTO DeliveryRates VALUES (1, 251, 500, 2.22); INSERT INTO DeliveryRates VALUES (1, 501, 750, 2.62); INSERT INTO DeliveryRates VALUES (2, 0, 1000, 3.85); INSERT INTO DeliveryRates VALUES (2, 1001, 1500, 4.95); INSERT INTO DeliveryRates VALUES (2, 1501, 2000, 5.68); INSERT INTO DeliveryRates VALUES (2, 2001, 4000, 8.24); Now the first problem is easy: SELECT DM.delivery_code, DR.delivery_rate FROM Delivery_Methods AS DM, Delivery_Rates AS DR WHERE DR.delivery_code = DM.delivery_code AND @my_parcel_weight BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt; -----------------------------------------------Reply----------------------------------------------- Opps! Second part, with Standard SQL: SELECT delivery_code, delivery_rate AS best_delivery_rate FROM (SELECT DM.delivery_code, DR.delivery_rate, ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn FROM Delivery_Methods AS DM, Delivery_Rates AS DR WHERE DR.delivery_code = DM.delivery_code AND @my_parcel_weight BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) AS X (delivery_code, delivery_rate, rn) WHERE X.rn = 1; -----------------------------------------------Reply----------------------------------------------- In article <1180369929.281620.273@m36g2000hse.googlegroups.com>, Jason Lepack <jlep@gmail.com> writes >Solution to 1: <snip> Thanks, that's much better ;-) -- Alan Silver (anything added below this line is nothing to do with me) -----------------------------------------------Reply----------------------------------------------- In article <1180370270.054347.4@p77g2000hsh.googlegroups.com>, Jason Lepack <jlep@gmail.com> writes >Answer to #2: <snip> Duh! Obvious when you point it out! Thanks again -- Alan Silver (anything added below this line is nothing to do with me) -----------------------------------------------Reply----------------------------------------------- In article <1180371281.769676.196@g4g2000hsf.googlegroups.com>, --CELKO-- <jcelko@earthlink.net> writes >1) Do NOT use floating point numbers for currency; stick to >DECIMAL(s,p). Lot less work AND no rounding errors. OK, didn't know that, thanks >2) Instead of thinking in codes, think in data. I changed your names >a bit to align them with ISO-11179 Don't understand what you mean here. Were you referring to my use of "ID" in field names? If so, I don't see that "code" is so much better. Please explain what you were getting at. >3) While I applaud DEFAULTs, they are NOT a good idea in your CASE. They are there purely to allow me to use "not null" on the column. If you look at the INSERT statements, you will find that I don't actually use the default values. I always set default values for columns, but I don't think I ever use them. I just have a dislike of allowing nulls in a column unless it's really necessary, so I specify a default value to get around that. >4) You might consider using an abbreviation code for the delivery >methods so they would be easier for a human being to use. The method name is for the human, the ID/code is for the computer. How would using an abbreviation make it easier? >5) Minor points; you are using needlessly proprietary T-SQL; it is >INSERT INTO, Didn't know that, thanks. > you use ORDER BY in subqueries OK, my lack of expertise in SQL (which is why I came here!). Is this invalid, or just not a good idea? > AND put your DEFAULTs in >the wrong order. Please explain further as I'm not sure what you meant. <snip> >The real trick is to put in a weight range in your rates table: Well, it's a trick, but is it the real one? I'm not being sarcastic, I'm just wondering why it is so much better than just having the maximum weight. The other solution suggested was slightly more complex SQL, but not very much. Given the extra work of maintaining a database with a range of data rather than a single value (for example, requiring more validation between separate records for a specific delivery method), it might not be as superior as you imply. Then again, it might. Please explain further. <snip> Thanks very much for the reply. I'm genuinely interested in more explanation here, I wasn't being ungrateful or sarcastic. I don't claim to be an SQL expert (you'll be relieved to know) and am keenly interested in understanding more. Ta ra -- Alan Silver (anything added below this line is nothing to do with me) -----------------------------------------------Reply----------------------------------------------- But don't forget to add in your check constraints to ensure that there is no overlapping of weights, or else this whole solution falls to bits. ie: CREATE TABLE DeliveryMethods (delivery_code INTEGER NOT NULL PRIMARY KEY, delivery_description VARCHAR(50) NOT NULL); INSERT INTO DeliveryMethods VALUES (1, 'Royal Mail 2nd Class Recorded'); INSERT INTO DeliveryMethods VALUES (2, 'Royal Mail Standard Parcels'); CREATE TABLE DeliveryRates (delivery_code INTEGER NOT NULL REFERENCES DeliveryMethods(delivery_code), low_parcel_wgt INTEGER NOT NULL, high_parcel_wgt INTEGER NOT NULL, CHECK (low_parcel_wgt < high_parcel_wgt), delivery_rate DECIMAL(6,2) NOT NULL, PRIMARY KEY (delivery_code, low_parcel_wgt)); INSERT INTO DeliveryRates VALUES (1, 251, 500, 2.22); INSERT INTO DeliveryRates VALUES (1, 252, 499, 3.00); INSERT INTO DeliveryRates VALUES (2, 0, 1000, 3.85); declare @my_parcel_weight int set @my_parcel_weight = 300 SELECT DM.delivery_code, DR.delivery_rate FROM DeliveryMethods AS DM, DeliveryRates AS DR WHERE DR.delivery_code = DM.delivery_code AND @my_parcel_weight BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt; Cheers, Jason Lepack On May 28, 12:54 pm, --CELKO-- <jcelko@earthlink.net> wrote: - Hide quoted text - > 1) Do NOT use floating point numbers for currency; stick to > DECIMAL(s,p). Lot less work AND no rounding errors. > 2) Instead of thinking in codes, think in data. I changed your names > a bit to align them with ISO-11179 > 3) While I applaud DEFAULTs, they are NOT a good idea in your CASE. > 4) You might consider using an abbreviation code for the delivery > methods so they would be easier for a human being to use. > 5) Minor points; you are using needlessly proprietary T-SQL; it is > INSERT INTO, you use ORDER BY in subqueries AND put your DEFAULTs in > the wrong order. > CREATE TABLE DeliveryMethods > (delivery_code INTEGER NOT NULL PRIMARY KEY, > delivery_description VARCHAR(50) NOT NULL); > INSERT INTO DeliveryMethods VALUES (1, 'Royal Mail 2nd Class > Recorded'); > INSERT INTO DeliveryMethods VALUES (2, 'Royal Mail Standard Parcels'); > The real trick is to put in a weight range in your rates table: > CREATE TABLE DeliveryRates > (delivery_code INTEGER NOT NULL > REFERENCES DeliveryMethods(delivery_code), > low_parcel_wgt INTEGER NOT NULL, > high_parcel_wgt INTEGER NOT NULL, > CHECK (low_parcel_wgt < high_parcel_wgt), > delivery_rate DECIMAL(6,2) NOT NULL, > PRIMARY KEY (delivery_code, low_parcel_wgt)); > INSERT INTO DeliveryRates VALUES (1, 0, 100, 1.62); > INSERT INTO DeliveryRates VALUES (1, 101, 250, 1.90); > INSERT INTO DeliveryRates VALUES (1, 251, 500, 2.22); > INSERT INTO DeliveryRates VALUES (1, 501, 750, 2.62); > INSERT INTO DeliveryRates VALUES (2, 0, 1000, 3.85); > INSERT INTO DeliveryRates VALUES (2, 1001, 1500, 4.95); > INSERT INTO DeliveryRates VALUES (2, 1501, 2000, 5.68); > INSERT INTO DeliveryRates VALUES (2, 2001, 4000, 8.24); > Now the first problem is easy: > SELECT DM.delivery_code, DR.delivery_rate > FROM Delivery_Methods AS DM, > Delivery_Rates AS DR > WHERE DR.delivery_code = DM.delivery_code > AND @my_parcel_weight > BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt; Your coding style is all wrong, nobody uses the old cross join with restriction in the where clause, rather - the old way we used to write before ANSI 92. > FROM Delivery_Methods AS DM, > Delivery_Rates AS DR > WHERE DR.delivery_code = DM.delivery_code > AND @my_parcel_weight > BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) NO ! FROM <table> AS <alias> INNER JOIN <table> AS <alias> ON <join condition> WHERE <filter condition> Take a leaf out of your own book celko - use a style that is familiar to all and not to just yourself. Catch up with the rest of us - you are 7 years behind. If you check books online and any books on SQL Server you will not find your style of coding joins anywhere. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "--CELKO--" <jcelko@earthlink.net> wrote in message news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... - Hide quoted text - > Opps! Second part, with Standard SQL: > SELECT delivery_code, delivery_rate AS best_delivery_rate > FROM (SELECT DM.delivery_code, DR.delivery_rate, > ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn > FROM Delivery_Methods AS DM, > Delivery_Rates AS DR > WHERE DR.delivery_code = DM.delivery_code > AND @my_parcel_weight > BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) > AS X (delivery_code, delivery_rate, rn) > WHERE X.rn = 1; Celko, this is a SQL Server-specific forum. PLEASE STOP giving examples with the join syntax used below!! It has been explicitly stated by Microsoft that such syntax WILL NOT BE SUPPORTED in the future. -- TheSQLGuru President Indicium Resources, Inc. "--CELKO--" <jcelko@earthlink.net> wrote in message news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... - Hide quoted text - > Opps! Second part, with Standard SQL: > SELECT delivery_code, delivery_rate AS best_delivery_rate > FROM (SELECT DM.delivery_code, DR.delivery_rate, > ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn > FROM Delivery_Methods AS DM, > Delivery_Rates AS DR > WHERE DR.delivery_code = DM.delivery_code > AND @my_parcel_weight > BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) > AS X (delivery_code, delivery_rate, rn) > WHERE X.rn = 1; >> you use ORDER BY in subqueries > OK, my lack of expertise in SQL (which is why I came here!). Is this > invalid, or just not a good idea? This is a bad idea from a performance standpoint. The order by does nothing for the actual query but carries server overhead. -- TheSQLGuru President Indicium Resources, Inc. -----------------------------------------------Reply----------------------------------------------- I fully agree that old join syntax for inner joins is awkward, and we do have a nicer way to express such. I do not agree that these won't be supported (again, for inner joins). If you have some MS documentation that states this, please share so we can communicate with MS to clear up that documentation. (ANSI SQL supports old style inner joins in all standard revisions, and last time I talked to MS dev's there were no plans to deprecate this in SQL Server. Things might have changed since that, but I'd be surprised...) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi - Hide quoted text - "TheSQLGuru" <kgbo@earthlink.net> wrote in message news:OidEKyYoHHA.1776@TK2MSFTNGP05.phx.gbl... > Celko, this is a SQL Server-specific forum. PLEASE STOP giving examples with the join syntax used > below!! It has been explicitly stated by Microsoft that such syntax WILL NOT BE SUPPORTED in the > future. > -- > TheSQLGuru > President > Indicium Resources, Inc. > "--CELKO--" <jcelko@earthlink.net> wrote in message > news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... >> Opps! Second part, with Standard SQL: >> SELECT delivery_code, delivery_rate AS best_delivery_rate >> FROM (SELECT DM.delivery_code, DR.delivery_rate, >> ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn >> FROM Delivery_Methods AS DM, >> Delivery_Rates AS DR >> WHERE DR.delivery_code = DM.delivery_code >> AND @my_parcel_weight >> BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) >> AS X (delivery_code, delivery_rate, rn) >> WHERE X.rn = 1; Hi Tibor, Just to jump in here because this is an important topic that also shows direct weakness, inconsistency and ambiquity in arguments celko puts out on other techniques/methods of coding. From join fundementals in 2005 latest bol, here is where it states that the ANSI 92 INNER JOIN is the "preferred" method... Copy Code SELECT pv.ProductID, v.VendorID, v.Name FROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v ON (pv.VendorID = v.VendorID) WHERE StandardPrice > $10 AND Name LIKE N'F%' The previous examples specified the join conditions in the FROM clause, which is the preferred method. The following query contains the same join condition specified in the WHERE clause: Copy Code SELECT pv.ProductID, v.VendorID, v.Name FROM Purchasing.ProductVendor pv, Purchasing.Vendor v WHERE pv.VendorID = v.VendorID AND StandardPrice > $10 AND Name LIKE N'F%' --- From 'Using Joins'.... Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. --- These are just two quick to find references in the product manual (bol), it clearly states the INNER JOIN is the preferred and recommended method of syntax rather than the old FROM x, y. It doesn't state it's being deprecated though, but read into it what you like - when the product vendor a) recommends a specific syntax and b) writes all its examples in it then you'd be dam right daft and irresponsible to use the old syntax impo. Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in message news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl... - Hide quoted text - >I fully agree that old join syntax for inner joins is awkward, and we do >have a nicer way to express such. > I do not agree that these won't be supported (again, for inner joins). > If you have some MS documentation that states this, please share so we can > communicate with MS to clear up that documentation. > (ANSI SQL supports old style inner joins in all standard revisions, and > last time I talked to MS dev's there were no plans to deprecate this in > SQL Server. Things might have changed since that, but I'd be surprised...) > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > "TheSQLGuru" <kgbo@earthlink.net> wrote in message > news:OidEKyYoHHA.1776@TK2MSFTNGP05.phx.gbl... >> Celko, this is a SQL Server-specific forum. PLEASE STOP giving examples >> with the join syntax used below!! It has been explicitly stated by >> Microsoft that such syntax WILL NOT BE SUPPORTED in the future. >> -- >> TheSQLGuru >> President >> Indicium Resources, Inc. >> "--CELKO--" <jcelko@earthlink.net> wrote in message >> news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... >>> Opps! Second part, with Standard SQL: >>> SELECT delivery_code, delivery_rate AS best_delivery_rate >>> FROM (SELECT DM.delivery_code, DR.delivery_rate, >>> ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn >>> FROM Delivery_Methods AS DM, >>> Delivery_Rates AS DR >>> WHERE DR.delivery_code = DM.delivery_code >>> AND @my_parcel_weight >>> BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) >>> AS X (delivery_code, delivery_rate, rn) >>> WHERE X.rn = 1; In article <undYTzYoHHA.4@TK2MSFTNGP05.phx.gbl>, TheSQLGuru <kgbo@earthlink.net> writes >>> you use ORDER BY in subqueries >> OK, my lack of expertise in SQL (which is why I came here!). Is this >> invalid, or just not a good idea? >This is a bad idea from a performance standpoint. The order by does nothing >for the actual query but carries server overhead. Got it now, thanks. I included the order by as I was using "top 1" to get the lowest rate. Surely if I left it out, I might not get the lowest rate, which was the point of the code. Did I miss something? Thanks again -- Alan Silver (anything added below this line is nothing to do with me) -----------------------------------------------Reply----------------------------------------------- What about MIN? ML --- http://milambda.blogspot.com/ -----------------------------------------------Reply----------------------------------------------- >> I do not agree that these won't be supported (again, for inner joins). If you have some MS documentation that states this, please share so we can communicate with MS to clear up that documentation.<< Why would any vendor needlessly destroy existing code by violating ANSI/ISO conformance? They got rid of the old =, (+) and OUTER syntaxes for LEFT OUTER JOIN to get to ANSI/ISO conformance. -----------------------------------------------Reply----------------------------------------------- More to the point, why aren't you following vendor definied recommendations and best practice? It clearly states in books online the preferred method is FROM <table> INNER JOIN <table> ON <join clause> and not FROM <table>, <table> WHERE <join clause>. Do you not see how irresponsible you are being in continuing your style recommendation against the vendor docs? -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "--CELKO--" <jcelko@earthlink.net> wrote in message news:1180445114.981804.63160@q69g2000hsb.googlegroups.com... - Hide quoted text - >>> I do not* agree that these won't be supported (again, for inner joins). >>> If you have some MS documentation that states this, please share so we >>> can communicate with MS to clear up that documentation.<< > Why would any vendor needlessly destroy existing code by violating > ANSI/ISO conformance? They got rid of the old =, (+) and OUTER > syntaxes for LEFT OUTER JOIN to get to ANSI/ISO conformance. Hey Tony! I'm not recommending the old join syntax, I hope my post suggested that. All I was saying was that old join syntax isn't deprecated* for inner joins. Personally, I haven't written an old style inner join for ages. :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Tony Rogerson" <tonyroger@torver.net> wrote in message news:egZ2cheoHHA.5052@TK2MSFTNGP04.phx.gbl... - Hide quoted text - > Hi Tibor, > Just to jump in here because this is an important topic that also shows direct weakness, > inconsistency and ambiquity in arguments celko puts out on other techniques/methods of coding. > From join fundementals in 2005 latest bol, here is where it states that the ANSI 92 INNER JOIN is > the "preferred" method... > Copy Code > SELECT pv.ProductID, v.VendorID, v.Name > FROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v > ON (pv.VendorID = v.VendorID) > WHERE StandardPrice > $10 > AND Name LIKE N'F%' > The previous examples specified the join conditions in the FROM clause, which is the preferred > method. The following query contains the same join condition specified in the WHERE clause: > Copy Code > SELECT pv.ProductID, v.VendorID, v.Name > FROM Purchasing.ProductVendor pv, Purchasing.Vendor v > WHERE pv.VendorID = v.VendorID > AND StandardPrice > $10 > AND Name LIKE N'F%' > --- > From 'Using Joins'.... > Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM > clause is recommended. > --- > These are just two quick to find references in the product manual (bol), it clearly states the > INNER JOIN is the preferred and recommended method of syntax rather than the old FROM x, y. > It doesn't state it's being deprecated though, but read into it what you like - when the product > vendor a) recommends a specific syntax and b) writes all its examples in it then you'd be dam > right daft and irresponsible to use the old syntax impo. > Tony. > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > "Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in message > news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl... >>I fully agree that old join syntax for inner joins is awkward, and we do have a nicer way to >>express such. >> I do not agree that these won't be supported (again, for inner joins). If you have some MS >> documentation that states this, please share so we can communicate with MS to clear up that >> documentation. >> (ANSI SQL supports old style inner joins in all standard revisions, and last time I talked to MS >> dev's there were no plans to deprecate this in SQL Server. Things might have changed since that, >> but I'd be surprised...) >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> "TheSQLGuru" <kgbo@earthlink.net> wrote in message >> news:OidEKyYoHHA.1776@TK2MSFTNGP05.phx.gbl... >>> Celko, this is a SQL Server-specific forum. PLEASE STOP giving examples with the join syntax >>> used below!! It has been explicitly stated by Microsoft that such syntax WILL NOT BE SUPPORTED >>> in the future. >>> -- >>> TheSQLGuru >>> President >>> Indicium Resources, Inc. >>> "--CELKO--" <jcelko@earthlink.net> wrote in message >>> news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... >>>> Opps! Second part, with Standard SQL: >>>> SELECT delivery_code, delivery_rate AS best_delivery_rate >>>> FROM (SELECT DM.delivery_code, DR.delivery_rate, >>>> ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn >>>> FROM Delivery_Methods AS DM, >>>> Delivery_Rates AS DR >>>> WHERE DR.delivery_code = DM.delivery_code >>>> AND @my_parcel_weight >>>> BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) >>>> AS X (delivery_code, delivery_rate, rn) >>>> WHERE X.rn = 1; >> Do you not see how irresponsible you are being in continuing your style recommendation against the vendor docs? << "Caesar: Pardon him, Theodotus. He is a barbarian and thinks the customs of his tribe and island are the laws of nature." - Caesar and Cleopatra; George Bernard Shaw 1898 This vendor finally dropped the use of camelCase after realizing it is a bitch to read (the eye moves to the uppercase letter, then jerks back to the front of the word). I think I have stated clearly why I prefer the WHERE clause syntax over the infixed notation. I have given the history of the actions inside ANSI X3H2 and why the various infixed notations exist (even the ones that nobody should use like CORRESPONDING and NATURAL clauses). It is there for products without an optimizer, so that the order of execution can be explicitly controlled. I also explained the sequential mindset that has to have a left-to- right, fully controlled ordering using only binary operators versus a declarative, n-ary function mindset. The first mindset lets old VB or C# programmers who want to fake a file system in CLR feel good about his code without having to learn a new way of thinking. -----------------------------------------------Reply----------------------------------------------- Hi Tibor, Sorry, for some reason I read your post as if you where. Sounds like another beer I owe you - are you going to IT Forum / Teched in Nov aka Barcelona?? Tony -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in message news:OOquNCgoHHA.4132@TK2MSFTNGP02.phx.gbl... - Hide quoted text - > Hey Tony! > I'm not recommending the old join syntax, I hope my post suggested that. > All I was saying was that old join syntax isn't deprecated for inner > joins. Personally, I haven't written an old style inner join for ages. > :-) > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > "Tony Rogerson" <tonyroger@torver.net> wrote in message > news:egZ2cheoHHA.5052@TK2MSFTNGP04.phx.gbl... >> Hi Tibor, >> Just to jump in here because this is an important topic that also shows >> direct weakness, inconsistency and ambiquity in arguments celko puts out >> on other techniques/methods of coding. >> From join fundementals in 2005 latest bol, here is where it states that >> the ANSI 92 INNER JOIN is the "preferred" method... >> Copy Code >> SELECT pv.ProductID, v.VendorID, v.Name >> FROM Purchasing.ProductVendor pv JOIN Purchasing.Vendor v >> ON (pv.VendorID = v.VendorID) >> WHERE StandardPrice > $10 >> AND Name LIKE N'F%' >> The previous examples specified the join conditions in the FROM clause, >> which is the preferred method. The following query contains the same join >> condition specified in the WHERE clause: >> Copy Code >> SELECT pv.ProductID, v.VendorID, v.Name >> FROM Purchasing.ProductVendor pv, Purchasing.Vendor v >> WHERE pv.VendorID = v.VendorID >> AND StandardPrice > $10 >> AND Name LIKE N'F%' >> --- >> From 'Using Joins'.... >> Join conditions can be specified in either the FROM or WHERE clauses; >> specifying them in the FROM clause is recommended. >> --- >> These are just two quick to find references in the product manual (bol), >> it clearly states the INNER JOIN is the preferred and recommended method >> of syntax rather than the old FROM x, y. >> It doesn't state it's being deprecated though, but read into it what you >> like - when the product vendor a) recommends a specific syntax and b) >> writes all its examples in it then you'd be dam right daft and >> irresponsible to use the old syntax impo. >> Tony. >> -- >> Tony Rogerson, SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson >> [Ramblings from the field from a SQL consultant] >> http://sqlserverfaq.com >> [UK SQL User Community] >> "Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote >> in message news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl... >>>I fully agree that old join syntax for inner joins is awkward, and we do >>>have a nicer way to express such. >>> I do not agree that these won't be supported (again, for inner joins). >>> If you have some MS documentation that states this, please share so we >>> can communicate with MS to clear up that documentation. >>> (ANSI SQL supports old style inner joins in all standard revisions, and >>> last time I talked to MS dev's there were no plans to deprecate this in >>> SQL Server. Things might have changed since that, but I'd be >>> surprised...) >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> "TheSQLGuru" <kgbo@earthlink.net> wrote in message >>> news:OidEKyYoHHA.1776@TK2MSFTNGP05.phx.gbl... >>>> Celko, this is a SQL Server-specific forum. PLEASE STOP giving >>>> examples with the join syntax used below!! It has been explicitly >>>> stated by Microsoft that such syntax WILL NOT BE SUPPORTED in the >>>> future. >>>> -- >>>> TheSQLGuru >>>> President >>>> Indicium Resources, Inc. >>>> "--CELKO--" <jcelko@earthlink.net> wrote in message >>>> news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... >>>>> Opps! Second part, with Standard SQL: >>>>> SELECT delivery_code, delivery_rate AS best_delivery_rate >>>>> FROM (SELECT DM.delivery_code, DR.delivery_rate, >>>>> ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn >>>>> FROM Delivery_Methods AS DM, >>>>> Delivery_Rates AS DR >>>>> WHERE DR.delivery_code = DM.delivery_code >>>>> AND @my_parcel_weight >>>>> BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) >>>>> AS X (delivery_code, delivery_rate, rn) >>>>> WHERE X.rn = 1; Who gives a hoot WHY!?!? This is a MICROSOFT SQL SERVER FORUM, and Microsoft has explicitly stated DO NOT DO THIS. Feel free to tell OTHER forums you post on to use whatever you like for join syntax. But on THIS forum, it is quite bad practice to use the =, = and = syntax, IMNSHO. -- TheSQLGuru President Indicium Resources, Inc. "--CELKO--" <jcelko@earthlink.net> wrote in message news:1180445114.981804.63160@q69g2000hsb.googlegroups.com... - Hide quoted text - >>> I do not agree that these won't be supported (again, for inner joins). >>> If you have some MS documentation that states this, please share so we >>> can communicate with MS to clear up that documentation.<< > Why would any vendor needlessly destroy existing code by violating > ANSI/ISO conformance? They got rid of the old =, (+) and OUTER > syntaxes for LEFT OUTER JOIN to get to ANSI/ISO conformance. select from sys.objects o, sys.columns c where o.objectid = c.objectid RESULT: Msg 4147, Level 15, State 1, Line 3 The query uses non-ANSI outer join operators ("=" or "="). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. I took the last sentence of that to mean ALL "non-ANSI join operators", not just LEFT (=) and RIGHT (=) ones since it is a new, full sentence. Of course I could be wrong in that interpretation, but a proper wording if it means just outer would be to state "...non-ANSI outer join operators...". -- TheSQLGuru President Indicium Resources, Inc. "Tibor Karaszi" <tibor_please.no.email_kara@hotmail.nomail.com> wrote in message news:%23aXUrWdoHHA.2452@TK2MSFTNGP02.phx.gbl... - Hide quoted text - >I fully agree that old join syntax for inner joins is awkward, and we do >have a nicer way to express such. > I do not* agree that these won't be supported (again, for inner joins). > If you have some MS documentation that states this, please share so we can > communicate with MS to clear up that documentation. > (ANSI SQL supports old style inner joins in all standard revisions, and > last time I talked to MS dev's there were no plans to deprecate this in > SQL Server. Things might have changed since that, but I'd be surprised...) > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > "TheSQLGuru" <kgbo@earthlink.net> wrote in message > news:OidEKyYoHHA.1776@TK2MSFTNGP05.phx.gbl... >> Celko, this is a SQL Server-specific forum. PLEASE STOP giving examples >> with the join syntax used below!! It has been explicitly stated by >> Microsoft that such syntax WILL NOT BE SUPPORTED in the future. >> -- >> TheSQLGuru >> President >> Indicium Resources, Inc. >> "--CELKO--" <jcelko@earthlink.net> wrote in message >> news:1180371544.787886.92210@o5g2000hsb.googlegroups.com... >>> Opps! Second part, with Standard SQL: >>> SELECT delivery_code, delivery_rate AS best_delivery_rate >>> FROM (SELECT DM.delivery_code, DR.delivery_rate, >>> ROW_NUMBER() OVER (ORDER BY DR.delivery_rate) AS rn >>> FROM Delivery_Methods AS DM, >>> Delivery_Rates AS DR >>> WHERE DR.delivery_code = DM.delivery_code >>> AND @my_parcel_weight >>> BETWEEN DR.low_parcel_wgt AND DR.high_parcel_wgt) >>> AS X (delivery_code, delivery_rate, rn) >>> WHERE X.rn = 1; My bad! I missed the top1/orderby! Sorry. As ML suggested, MIN operator is more appropriate since that is what you truly seek here. -- TheSQLGuru President Indicium Resources, Inc. "Alan Silver" <alan-sil@nospam.thanx.invalid> wrote in message news:htJRkb6MDCXGFwcH@nospamthankyou.spam... - Hide quoted text - > In article <undYTzYoHHA.4@TK2MSFTNGP05.phx.gbl>, TheSQLGuru > <kgbo@earthlink.net> writes >>>> you use ORDER BY in subqueries >>> OK, my lack of expertise in SQL (which is why I came here!). Is this >>> invalid, or just not a good idea? >>This is a bad idea from a performance standpoint. The order by does >>nothing >>for the actual query but carries server overhead. > Got it now, thanks. > I included the order by as I was using "top 1" to get the lowest rate. > Surely if I left it out, I might not get the lowest rate, which was the > point of the code. > Did I miss something? Thanks again > -- > Alan Silver > (anything added below this line is nothing to do with me) In article <#19d17ioHHA.3@TK2MSFTNGP04.phx.gbl>, TheSQLGuru <kgbo@earthlink.net> writes >My bad! I missed the top1/orderby! Sorry. >As ML suggested, MIN operator is more appropriate since that is what >you truly seek here. Thanks for the clarification -- Alan Silver (anything added below this line is nothing to do with me)