Re: (ot) SQL query aliases in SQL Management Studio
It looks like the reason for the original rewrite was due to subquerying the same table twice. Nothing wrong with that, but the wizard decides to ensure that every reference is unique, thus the aliasing of the second reference. You could get around that by adding your own table aliases in the subqueries: SELECT o.OpportunityKeyID, o.OpportunityName, F1.CustomFieldValue AS MyCustomField1, F2.CustomFieldValue AS MyCustomField2 FROM tblOpportunities AS o LEFT OUTER JOIN ( SELECT OpportunityKeyID, CustomFieldValue FROM dbo.tvw_CustomOpportunityFieldData AS of1 --NOTE the table alias here WHERE OrdinalPosition = 1 ) AS F1 ON F1.OpportunityKeyID = o.OpportunityKeyID LEFT OUTER JOIN ( SELECT OpportunityKeyID, CustomFieldValue FROM dbo.tvw_CustomOpportunityFieldData AS of2 --NOTE the table alias here WHERE OrdinalPosition = 2 ) AS F2 ON F2.OpportunityKeyID = o.OpportunityKeyID ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329322 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL query aliases in SQL Management Studio
> I would recommended creating the view from a query window, > and not the > design view that SQL Management studio uses +1 . Traditionally design views and wizards do unspeakable things to your nicely formatted sql. -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329312 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) SQL query aliases in SQL Management Studio
Thanks Dave. Worked like a charm. I'll just have to annotate the SQL so future modifications to this View don't cause the same problem I've been fighting. -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Tuesday, December 22, 2009 10:48 AM To: cf-talk Subject: Re: (ot) SQL query aliases in SQL Management Studio Hi Jason, I would recommended creating the view from a query window, and not the design view that SQL Management studio uses Simply add a 'Create View dbo. AS' before the statement that works and run it in the query window Hope this helps -Dave On Tue, Dec 22, 2009 at 11:34 AM, Jason Durham wrote: > > My apologies for posting an OT message to a ColdFusion list. RDMS are so > closely tied to our day-to-day job in writing CF, that perhaps the list will > be tolerant of such a question. > > I'm writing a View for our CRM package to use. The SQL works as I've > written it, but Management Studio keeps aliasing my tables and I can't find > a way to either a) prevent Management Studio from adding the aliases or b) > correctly write my own aliases which returns the proper data. If I execute > the SQL as written, it works. However, after it's executed the first time, > the View referenced in the 2nd JOIN is aliased and causes Type> to be returned for MyCustomField2 on subsequent executions. > > The query is quite large, but I've simplified it for this list. > > WORKING SQL > > SELECT dbo.tblOpportunities.OpportunityKeyID, > dbo.tblOpportunties.OpportunityName, > F1.CustomFieldValue AS MyCustomField1, > F2.CustomFieldValue AS MyCustomField2 > FROM tblOpportunities > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 1 > ) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 2 > ) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > > > > > SQL MANAGEMENT STUDIO ALIAS ADDED > > SELECT dbo.tblOpportunities.OpportunityKeyID, > dbo.tblOpportunties.OpportunityName, > F1.CustomFieldValue AS MyCustomField1, > F2.CustomFieldValue AS MyCustomField2 > FROM tblOpportunities > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 1 > ) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData AS > tvw_CustomOpportunityFieldData_2 /** THIS WAS ALTERED ***/ >WHERE OrdinalPosition = 2 > ) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > > > > > Confidentiality Warning: This e-mail contains information intended only for > the use of the individual or entity named above. If the reader of this > e-mail is not the intended recipient or the employee or agent responsible > for delivering it to the intended recipient, any dissemination, publication > or copying of this e-mail is strictly prohibited. The sender does not accept > any responsibility for any loss, disruption or damage to your data or > computer system that may occur while using data contained in, or transmitted > with, this e-mail. If you have received this e-mail in error, please > immediately notify us by return e-mail. > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329311 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) SQL query aliases in SQL Management Studio
Hi Jason, I would recommended creating the view from a query window, and not the design view that SQL Management studio uses Simply add a 'Create View dbo. AS' before the statement that works and run it in the query window Hope this helps -Dave On Tue, Dec 22, 2009 at 11:34 AM, Jason Durham wrote: > > My apologies for posting an OT message to a ColdFusion list. RDMS are so > closely tied to our day-to-day job in writing CF, that perhaps the list will > be tolerant of such a question. > > I'm writing a View for our CRM package to use. The SQL works as I've > written it, but Management Studio keeps aliasing my tables and I can't find > a way to either a) prevent Management Studio from adding the aliases or b) > correctly write my own aliases which returns the proper data. If I execute > the SQL as written, it works. However, after it's executed the first time, > the View referenced in the 2nd JOIN is aliased and causes Type> to be returned for MyCustomField2 on subsequent executions. > > The query is quite large, but I've simplified it for this list. > > WORKING SQL > > SELECT dbo.tblOpportunities.OpportunityKeyID, > dbo.tblOpportunties.OpportunityName, > F1.CustomFieldValue AS MyCustomField1, > F2.CustomFieldValue AS MyCustomField2 > FROM tblOpportunities > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 1 > ) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 2 > ) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > > > > > SQL MANAGEMENT STUDIO ALIAS ADDED > > SELECT dbo.tblOpportunities.OpportunityKeyID, > dbo.tblOpportunties.OpportunityName, > F1.CustomFieldValue AS MyCustomField1, > F2.CustomFieldValue AS MyCustomField2 > FROM tblOpportunities > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData >WHERE OrdinalPosition = 1 > ) AS F1 ON F1.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > LEFT OUTER JOIN >( > SELECT OpportunityKeyID, CustomFieldValue >FROM dbo.tvw_CustomOpportunityFieldData AS > tvw_CustomOpportunityFieldData_2 /** THIS WAS ALTERED ***/ >WHERE OrdinalPosition = 2 > ) AS F2 ON F2.OpportunityKeyID = dbo.tblOpportunities.OpportunityKeyID > > > > > Confidentiality Warning: This e-mail contains information intended only for > the use of the individual or entity named above. If the reader of this > e-mail is not the intended recipient or the employee or agent responsible > for delivering it to the intended recipient, any dissemination, publication > or copying of this e-mail is strictly prohibited. The sender does not accept > any responsibility for any loss, disruption or damage to your data or > computer system that may occur while using data contained in, or transmitted > with, this e-mail. If you have received this e-mail in error, please > immediately notify us by return e-mail. > > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329309 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Query Question
Welcome... I'm sure Jochem's works well too -- assuming the db supports a natural join on an aliased sub-query ... I honestly had never seen that before, so I have no idea what the support for it is like with various db's. But it did peak my curiosity. :) > I knew there was any easier way to do this. > Thanks > -- Jeff > > From: S. Isaac Dealey <[EMAIL PROTECTED]> > Sent: Friday, May 20, 2005 9:16 AM > To: CF-Talk > Subject: Re: OT: SQL Query Question > select t.* from test t where t.actid in > (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) > ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207279 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
I knew there was any easier way to do this. Thanks -- Jeff From: S. Isaac Dealey <[EMAIL PROTECTED]> Sent: Friday, May 20, 2005 9:16 AM To: CF-Talk Subject: Re: OT: SQL Query Question select t.* from test t where t.actid in (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207275 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
Jeff Chastain wrote: > > I have a table that looks something like this > > crIDactIDdescriptionowner > 1 1Test 444 > 1 2Test Update124 > 2 1Test 578 > > Now, what I need is a listing of all distinct crID records, where the actID > is the greatest. Basically, this is a history setup. There are a series of > change requests (crID) that each have 1 or more actions (actID). I need a > snapshot of the most recent status of each change request ( max(actID) ). > The problem I am having is with the aggregate functions and getting all of > the rest of the fields at the same time. The following query returns the > correct crID / actID combination, but how do I get the other fields? > > SELECT crID, MAX(actID) AS actID > FROM test > GROUP BY crID By joining this result to the original table: SELECT a.* FROMtest a NATURAL JOIN ( SELECT crID, MAX(actID) AS actID FROM test GROUP BY crID) b Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207269 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query Question
select t.* from test t where t.actid in (SELECT MAX(t2.actID) FROM test t2 where t2.crID = t.crID) ought to work... s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207259 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL/Query exestuation plans
Ian Skinner wrote: > I remember a thread recently where DBMS execution plans where used to debug a > problematic query. I now have a need to explore this on an Oracle 8/9 system > for documentation and debugging. I'm off to Google to see what I can find > out on how one does this with Oracle systems. But I though I would also ask > here since this group can often answer questions faster and more usefully > then what I can Google up easily. > > So how does one actually capture and view these execution plans with an > Oracle DBMS? First you need a plan_table. It can be generated from the utlxplan.sql script in rdbms/admin if it isn't there yet. Next, start a transaction, delete all rows from the plan_table, run your query with "EXPLAIN PLAN FOR " prepended which will populate the table, display the result from plan_table and rollback. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195040 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Query
Shouldn't that be a left join, since you always want to include the levels information? Adam H On Fri, 13 Aug 2004 16:50:25 -0500, Paul Giesenhagen <[EMAIL PROTECTED]> wrote: > I tried to ask this on Experts Exchange without any luck ... maybe one of you guys may be able to help out. > > I am trying to build some pricing levels into our application and having a hard time with SQL query, here is an explination. > > I have 2 tables: > > LEVELS > > levelID, code, description, status > > LEVELPRICING > > levelPriceID, levelID, productID, Price > > I am running the following query: > = > SELECT l.levelID, l.code, l.description, p.price > FROM #request.levelTable# l > RIGHT OUTER JOIN #request.levelPricingTable# p > ON l.levelID = p.levelID > WHERE (p.productID = #url.productID# > OR p.productID IS NULL) > AND l.status = 1 > = > > Basically I have the tables above (let me populate some data: > > Levels > levelID, code, description, status > 1 555 Wholesale 1 > 2 444 Gold Member 1 > 3222 Silver Member 0 > > LevelPricing > levelPriceID, levelID, productID, Price > 1 1 18 30.00 > 2 1 29 25.00 > 3 2 18 25.00 > 4 2 4227.00 > > I want to pull out WHOLESALE AND GOLD MEMBER (Silver is status 0 so it is turned off). > > I should be pulling out 2 records IF I am passing URL.PRODUCTID = 18, I should have 30.00 and 25.00 for wholesale and gold. > > IF my url.productID is 0 (a new product page). Then I STILL want to pull out the two rows for WHOLESALE and GOLD MEMBER, but the price should be NULL (since there is not a record for productID 0 in the levelPricing table. > > SO for productID = 18 > l.levelID, l.code, l.description, p.price > 1 555 Wholesale 30.00 > 2444 Gold Member 25.00 > > For ProductID = 0 > l.levelID, l.code, l.description, p.price > 1 555 Wholesale NULL > 2444 Gold Member NULL > > On the product building page I would like to list out all the various descriptions of level pricings from the levels table. IF the productID matches up, I would like to output the associated price with the level. IF productID is 0 (or a new record), I would like it to list out the levels.description with a NULL price. > > This query is not working, if there are prices associated with the productID it shows the levels, but if there are not pricing associated with the productID it doesn't show the various levels. > > Any suggestions? I have tried multiple variations without avail. (Needs to work in MS Access and MS SQL) > > Paul Giesenhagen > QuillDesign > 417-885-1375 > http://www.quilldesign.com > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: OT: SQL Query
Thanks Jochem,
following worked correctly, I'm only dealing with about 100 records so I
does not appear to hinder performance at all.
SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
tbl_event.Event_Date, tbl_event.Event_Title,
tbl_location.location_Name
FROM tbl_event, tbl_location
WHERE 1 = 1
AND (tbl_event.Event_LocationID = tbl_location.location_ID
OR tbl_event.Event_LocationID = 1000)
AND tbl_event.Event_date > #now()#
AND (SELECT COUNT(e.Event_LocationID)
FROM tbl_Event e
WHERE e.Event_Date < tbl_event.Event_date
AND tbl_event.Event_LocationID = e.Event_LocationID
OR tbl_event.Event_LocationID = 1000
) < 4
ORDER BY tbl_event.Event_Date ASC
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23, 2003 10:05 AM
To: CF-Talk
Subject: Re: OT: SQL Query
Ryan Roskilly said:
>
>
> SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
> tbl_event.Event_Date, tbl_event.Event_Title,
> tbl_location.location_Name
> FROM tbl_event, tbl_location
> WHERE 1 = 1
> AND (tbl_event.Event_LocationID = tbl_location.location_ID
> OR tbl_event.Event_LocationID = 1000)
> AND tbl_event.Event_date > #now()#
> AND tbl_event.Event_date < #dateadd("m",5,now())#
AND (SELECT COUNT(e.Event_LocationID)
FROM tbl_Event e
WHERE e.Event_Date < tbl_event.Event_date
AND tbl_event.Event_LocationID = e.Event_LocationID
) < 3
> ORDER BY tbl_event.Event_Date ASC
>
> Is this even possible in one query?
It should be, but performance might be horrible.
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: OT: SQL Query
Ryan Roskilly said:
>
>
> SELECT tbl_event.Event_ID, tbl_event.Event_LocationID,
> tbl_event.Event_Date, tbl_event.Event_Title,
> tbl_location.location_Name
> FROM tbl_event, tbl_location
> WHERE 1 = 1
> AND (tbl_event.Event_LocationID = tbl_location.location_ID
> OR tbl_event.Event_LocationID = 1000)
> AND tbl_event.Event_date > #now()#
> AND tbl_event.Event_date < #dateadd("m",5,now())#
AND (SELECT COUNT(e.Event_LocationID)
FROM tbl_Event e
WHERE e.Event_Date < tbl_event.Event_date
AND tbl_event.Event_LocationID = e.Event_LocationID
) < 3
> ORDER BY tbl_event.Event_Date ASC
>
> Is this even possible in one query?
It should be, but performance might be horrible.
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: OT: SQL Query
No, this query still isn't working .. what the problem is some of the dates are the same ie there may be three reports on 10/22/2003 and 0 on 10/23/2003 and 1 on 10/24/2003 So the question is this, since my dates are duplicated, this may be impossible correct? What other solutions could be possible? I would hate to put in a sort method as below, it would be unconventional .. though it would work ... and it would then sync everything up .. would this be poor practice? I am assuming yes. IF not, would there be a better way to do it? SELECT reportID FROM beaverReports ORDER BY tripDate DESC UPDATE beaverReports SET tripSort = #thisQuery.currentRow# WHERE reportID = #thisQuery.reportID# Paul Giesenhagen QuillDesign - Original Message - From: Tony Weeg To: CF-Talk Sent: Friday, October 17, 2003 12:01 PM Subject: RE: OT: SQL Query does this work? SELECT TOP 1 reportID FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT reportID FROM beaverReports WHERE reportID = #variables.thisReport# UNION SELECT TOP 1 reportID FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) ORDER BY tripDate same thing minus the beginning ( and ending ) you had followed by a straggler order by tripDate? ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 12:40 PM To: CF-Talk Subject: Re: OT: SQL Query Ok here is the query that I have ... and the error that is showing up. I am using MS SQL 2000 ( SELECT TOP 1 reportID FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT reportID FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT TOP 1 reportID FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) ) ORDER BY tripDate ERROR: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near ')'. SQL = "SELECT TOP 1 * FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = 1032) UNION SELECT * FROM beaverReports WHERE reportID = 1032) UNION SELECT TOP 1 * FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = 1032) ORDER BY tripDate" Thanks for the help! Paul Giesenhagen QuillDesign - Original Message - From: Jochem van Dieten To: CF-Talk Sent: Friday, October 17, 2003 3:56 AM Subject: Re: OT: SQL Query Paul Giesenhagen said: > > I have a number of reports all dated, but dated by day (no time). > Some of the reports are on the same day If I have an ID value, I > need to grab the report before and the report after that ID and it > needs to be by date. > > Here is what the data looks like: > > ReportID | ReportDate | Report | > 19/10/2003 > 29/11/2003 > 39/11/2003 > 49/11/2003 > 59/12/2003 > 89/12/2003 > 99/14/2003 > 1110/01/2003 etc.. > > If I am looking at ReportID (5), then I want to know that reportID 4 > is previous and reportID 8 is next > > So my output should be > Previous = 4 > This > > Next = 8 > > Oh, and I need to know if previous is Nothing or Next is nothing (ie > there isn't anymore eitherway). It won't be evry fast, but should be acceptable if the dataset isn't too large. The preliminary resultsets need to be limited to one row, the code for that is between square brackets (database dependent). ( SELECT [TOP 1] * FROM table WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate, ID [LIMIT 1] UNION SELECT * FROM table WHERE ID = 5) UNION SELECT [TOP 1] * FROM table WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate DESC, ID DESC [LIMIT 1] ) ORDER BY ReportDate Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Query
does this work? SELECT TOP 1 reportID FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT reportID FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT TOP 1 reportID FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) ORDER BY tripDate same thing minus the beginning ( and ending ) you had followed by a straggler order by tripDate? ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 12:40 PM To: CF-Talk Subject: Re: OT: SQL Query Ok here is the query that I have ... and the error that is showing up. I am using MS SQL 2000 ( SELECT TOP 1 reportID FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT reportID FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT TOP 1 reportID FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) ) ORDER BY tripDate ERROR: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near ')'. SQL = "SELECT TOP 1 * FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = 1032) UNION SELECT * FROM beaverReports WHERE reportID = 1032) UNION SELECT TOP 1 * FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = 1032) ORDER BY tripDate" Thanks for the help! Paul Giesenhagen QuillDesign - Original Message - From: Jochem van Dieten To: CF-Talk Sent: Friday, October 17, 2003 3:56 AM Subject: Re: OT: SQL Query Paul Giesenhagen said: > > I have a number of reports all dated, but dated by day (no time). > Some of the reports are on the same day If I have an ID value, I > need to grab the report before and the report after that ID and it > needs to be by date. > > Here is what the data looks like: > > ReportID | ReportDate | Report | > 19/10/2003 > 29/11/2003 > 39/11/2003 > 49/11/2003 > 59/12/2003 > 89/12/2003 > 99/14/2003 > 1110/01/2003 etc.. > > If I am looking at ReportID (5), then I want to know that reportID 4 > is previous and reportID 8 is next > > So my output should be > Previous = 4 > This > > Next = 8 > > Oh, and I need to know if previous is Nothing or Next is nothing (ie > there isn't anymore eitherway). It won't be evry fast, but should be acceptable if the dataset isn't too large. The preliminary resultsets need to be limited to one row, the code for that is between square brackets (database dependent). ( SELECT [TOP 1] * FROM table WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate, ID [LIMIT 1] UNION SELECT * FROM table WHERE ID = 5) UNION SELECT [TOP 1] * FROM table WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate DESC, ID DESC [LIMIT 1] ) ORDER BY ReportDate Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Query
Ok here is the query that I have ... and the error that is showing up. I am using MS SQL 2000 ( SELECT TOP 1 reportID FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT reportID FROM beaverReports WHERE reportID = #variables.thisReport#) UNION SELECT TOP 1 reportID FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = #variables.thisReport#) ) ORDER BY tripDate ERROR: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near ')'. SQL = "SELECT TOP 1 * FROM beaverReports WHERE tripDate > (SELECT tripDate FROM beaverReports WHERE reportID = 1032) UNION SELECT * FROM beaverReports WHERE reportID = 1032) UNION SELECT TOP 1 * FROM beaverReports WHERE tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = 1032) ORDER BY tripDate" Thanks for the help! Paul Giesenhagen QuillDesign - Original Message - From: Jochem van Dieten To: CF-Talk Sent: Friday, October 17, 2003 3:56 AM Subject: Re: OT: SQL Query Paul Giesenhagen said: > > I have a number of reports all dated, but dated by day (no time). > Some of the reports are on the same day If I have an ID value, I > need to grab the report before and the report after that ID and it > needs to be by date. > > Here is what the data looks like: > > ReportID | ReportDate | Report | > 19/10/2003 > 29/11/2003 > 39/11/2003 > 49/11/2003 > 59/12/2003 > 89/12/2003 > 99/14/2003 > 1110/01/2003 etc.. > > If I am looking at ReportID (5), then I want to know that reportID 4 > is previous and reportID 8 is next > > So my output should be > Previous = 4 > This > > Next = 8 > > Oh, and I need to know if previous is Nothing or Next is nothing (ie > there isn't anymore eitherway). It won't be evry fast, but should be acceptable if the dataset isn't too large. The preliminary resultsets need to be limited to one row, the code for that is between square brackets (database dependent). ( SELECT [TOP 1] * FROM table WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate, ID [LIMIT 1] UNION SELECT * FROM table WHERE ID = 5) UNION SELECT [TOP 1] * FROM table WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate DESC, ID DESC [LIMIT 1] ) ORDER BY ReportDate Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Query
The dataset is fairly small .. I doubt it would be over a few hundred records for awhile .. and in a few years maybe up to 2000, so I don't think it will be a huge ordeal. Thanks in advance, I will give it a try! Paul Giesenhagen QuillDesign - Original Message - From: Jochem van Dieten To: CF-Talk Sent: Friday, October 17, 2003 3:56 AM Subject: Re: OT: SQL Query Paul Giesenhagen said: > > I have a number of reports all dated, but dated by day (no time). > Some of the reports are on the same day If I have an ID value, I > need to grab the report before and the report after that ID and it > needs to be by date. > > Here is what the data looks like: > > ReportID | ReportDate | Report | > 19/10/2003 > 29/11/2003 > 39/11/2003 > 49/11/2003 > 59/12/2003 > 89/12/2003 > 99/14/2003 > 1110/01/2003 etc.. > > If I am looking at ReportID (5), then I want to know that reportID 4 > is previous and reportID 8 is next > > So my output should be > Previous = 4 > This > > Next = 8 > > Oh, and I need to know if previous is Nothing or Next is nothing (ie > there isn't anymore eitherway). It won't be evry fast, but should be acceptable if the dataset isn't too large. The preliminary resultsets need to be limited to one row, the code for that is between square brackets (database dependent). ( SELECT [TOP 1] * FROM table WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate, ID [LIMIT 1] UNION SELECT * FROM table WHERE ID = 5) UNION SELECT [TOP 1] * FROM table WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate DESC, ID DESC [LIMIT 1] ) ORDER BY ReportDate Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Query
Paul Giesenhagen said: > > I have a number of reports all dated, but dated by day (no time). > Some of the reports are on the same day If I have an ID value, I > need to grab the report before and the report after that ID and it > needs to be by date. > > Here is what the data looks like: > > ReportID | ReportDate | Report | > 19/10/2003 > 29/11/2003 > 39/11/2003 > 49/11/2003 > 59/12/2003 > 89/12/2003 > 99/14/2003 > 1110/01/2003 etc.. > > If I am looking at ReportID (5), then I want to know that reportID 4 > is previous and reportID 8 is next > > So my output should be > Previous = 4 > This > > Next = 8 > > Oh, and I need to know if previous is Nothing or Next is nothing (ie > there isn't anymore eitherway). It won't be evry fast, but should be acceptable if the dataset isn't too large. The preliminary resultsets need to be limited to one row, the code for that is between square brackets (database dependent). ( SELECT [TOP 1] * FROM table WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate, ID [LIMIT 1] UNION SELECT * FROM table WHERE ID = 5) UNION SELECT [TOP 1] * FROM table WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5) ORDER BY ReportDate DESC, ID DESC [LIMIT 1] ) ORDER BY ReportDate Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Query
Jeff Chastain wrote: > > Table 1 (key,name) - > 1,test > 2,test2 > > Table 2 (key,thread,timestamp) - > 1,1,1/1/2003 > 1,2,1/2/2003 > 2,3,1/1/2003 > 1,2,1/4/2003 > > The desired result set, passing in a key = 1 parameter, would be > > key,name,numthreads,timestamp > 1,test,2,1/4/2004 > > The difference I am seeing between this and your code is that it appears your code > counts the number of instances of a given key in table 2 instead of the number of > distinct threads per key in table 2. SELECT t1.key, t1.name, COUNT(DISTINCT t2.thread) AS numThreads MAX(t2.timeStamp) AS timestamp FROM t1 INNER JOIN t2 ON t1.key = t2.key GROUP BY t1.key, t1.name Jochem ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
re: OT: SQL Query
Jochem, Either I am not following your code or I may not have made myself clear. Take the following example ... Table 1 (key,name) - 1,test 2,test2 Table 2 (key,thread,timestamp) - 1,1,1/1/2003 1,2,1/2/2003 2,3,1/1/2003 1,2,1/4/2003 The desired result set, passing in a key = 1 parameter, would be key,name,numthreads,timestamp 1,test,2,1/4/2004 The difference I am seeing between this and your code is that it appears your code counts the number of instances of a given key in table 2 instead of the number of distinct threads per key in table 2. Thanks for the help. -- Jeff ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
Re: OT: SQL Query
Jeff Chastain wrote: > > I have two tables with a one to many relationship. I need to pull all the > records from table one with one field in the resulting recordset being a > count of how many corresponding records are in the second table and another > field being the date of the latest corresponding record in the second table. > > Table 1 - > - key (one) > - name > - alias > - etc... > > Table 2 - > - key (many) > - thread (count how many distinct ones exist per key) > - timeStamp (get latest per key) SELECT t1.key, COUNT(t2.key) AS recordNumber MAX(t2.timeStamp) AS latest FROM t1 INNER JOIN t2 ON t1.key = t2.key GROUP BY t1.key This presumes there is at least one entry in t2 for each entry in t1. Jochem ~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
Re: OT SQL query (dates)
I will dig into your suggestion (by default) it returns 0 records no matter what the form dates are. I truely appreciate your help! Paul Giesenhagen QuillDesign - Original Message - From: "Matthew Walker" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, September 16, 2002 11:28 PM Subject: RE: OT SQL query (dates) > How about... > > > SELECT sd.id > FROM settingTable AS sd, > settingTable AS ed > WHERE (sd.datatype="startdate") > AND (ed.datatype="enddate") > AND ( > (sd.datedata Between #form.startdate# > And #form.enddate#) > OR (ed.datedata Between #form.startdate# > And #form.enddate#) > OR ( > (#form.startdate# between > sd.datedata and ed.datedata) > and (#form.enddate# between > sd.datedata and ed.datedata) > ) > ) > > > -Original Message- > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, 17 September 2002 4:05 p.m. > > To: CF-Talk > > Subject: Re: OT SQL query (dates) > > > > > > There is only one startDate and one endDate ... > > > > So alittle more of an example: > > > > iddatatypesmalltextlongtextdatedata > > === > > 1dtValue my value > > 2dtValue2 Looong > > 3startDate10/01/2002 > > 4endDate 10/31/2002 > > 5dtValue3 Another > > ect... > > > > startDate and endDate datatypes don't have to correlate with > > the other rows > > (but they can). > > > > This table setup allows the use of generic data ... we use it > > for settings > > . since there are so many different types of settings, it is > > easy to write > > a query like this to get a value > > > > SELECT smalltext > > FROM settingsTable > > WHERE datatype = 'dtValue' > > > > #smalltext# = "my value" > > > > So our query is is looking at the startDate and the endDate > > datatypes for > > the datedata values. > > > > make sense? > > > > Paul Giesenhagen > > QuillDesign > > > > - Original Message - > > From: "Matthew Walker" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Monday, September 16, 2002 11:01 PM > > Subject: RE: OT SQL query (dates) > > > > > > > Are there only two rows inn your table? If not, how do you correlate > > > this startdate with that enddate? > > > > > > > -Original Message- > > > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > > > Sent: Tuesday, 17 September 2002 2:30 p.m. > > > > To: CF-Talk > > > > Subject: OT SQL query (dates) > > > > > > > > > > > > I have a bit of a challenge ... may be easy for someone, but > > > > I am missing the thought process here. > > > > > > > > I have a startdate and and enddate in the database.. I have a > > > > form that is submitting another start and end date. I need > > > > to write a query to check the start and end dates in the > > > > database, compare and make sure that the new start and end > > > > dates DO NOT OVERLAP... (plus the table is setup alittle oddly). > > > > > > > > So if I have in the db: > > > > > > > > startdate = 10/01/2002 > > > > enddate = 10/31/2002 > > > > > > > > and submitting form > > > > > > > > form.startdate = 10/15/2002 > > > > form.enddate = 10/20/2002 > > > > > > > > I want to be able to check and make set the recordcount to 1 > > > > (or a number other than 0) ...thus I will know that this is > > > > invalid because there is already a date range specified. if > > > > the form.startdate and form.enddate is after (or before) the > > > > DB's start and end dates, then give me a 0 recordcount and I > > > > know that it is valid. > > > > > > > > Below is the table setup and the data within it ... > > > > > > > > table is settingsTable > > > > iddatatypedatedata > > > > === > > > > 1startDate10/01/2002 > > > > 2endDate 10/31/2002 > > > > > > > > I tried this query > > > > > > > > SELECT sd.id > > > > FROM settingTable sd, settingTable ed > > > > WHERE sd.datedata <= #CreateODBCDate(form.promoStartDate)# > > > > AND ed.datedata >= #CreateODBCDate(form.promoEndDate)# > > > > AND sd.datatype = 'promoStartDate' > > > > AND ed.datatype = 'promoEndDate' > > > > > > > > But it always returns a 0 > > > > > > > > Any help would be greatly appreciated > > > > > > > > Paul Giesenhagen > > > > QuillDesign > > > > > > > > > > > > > > > > > > __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT: SQL Query
SELECT c.id, c.description
FROM choiceTable c
WHERE c.id = '#url.productid#'
AND NOT EXISTS (
SELECT pc.id
FROM ProductChoiceTable pc
WHERE pc.productid='#url.productid#'
)
--- Paul Giesenhagen <[EMAIL PROTECTED]> wrote:
> I have a query that I am trying to get all my
> descriptions from my choiceTable that are not found
> in my productChoiceTable that have a productid of a
> url.product value. Basically if a choice id is
> found in the productChoiceTable that also has the
> url.productid in the productid column, it is
> discarded in the result.
>
> Here is my table layout:
>
> choiceTable
> id, choice, description
>
> productChoiceTable
> id,choiceid,productid
>
>
> SELECT c.id, c.description
> FROM choiceTable c, productChoiceTable pc
> WHERE c.id = pc.choiceid
> AND pc.productid NOT IN ('#url.productid#')
>
> Any suggestions apreciated!
>
> Paul Giesenhagen
> QuillDesign
>
>
>
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT: SQL Query Depending on value
Yes, that is where the problem arises... Basically if inventory is > 0 show the item... no problems, but if the inventory level is <= 0 I need to see if backordering is allowed and if it is, show the item, if it isn't do not show the item. Make sense? Paul Giesenhagen QuillDesign > First thing I see, Paul is that you say > "if inventory is 0 or below, then I need to check backorder value" > > But your SQL says: > ... and inventory > 0 ... > > ??? > > Aren't these opposites? > > > On 5/20/02 8:38 PM, "Paul Giesenhagen" <[EMAIL PROTECTED]> wrote: > > > I am writing a join query that displays information depending on a value, > > > > There are two columns that deal with inventory, they are > > inventory = number in stock (could be a negative) > > backorder = either a 1/0 do we allow backordering or not, if it is 1 Yes, we > > allow backordering if 0 No we do not. > > > > The situation is, if inventory is 0 or below, then I need to check backorder > > value to see if it is a 1 or zero. If it is a 1, then I want to display that > > value, if it is a 0 and the inventory is 0 or below then do not show the > > value. > > > > Here is what I have so far! > > > > > > select o.type, v.id AS itemid, v.item, v.price > > from #caller.prefs.option_table# o, #caller.prefs.option_values_table# v > > where o.id = '#options.optionid#' > > and o.id = v.optionid > > and inventory > 0 > > and backorder = 1 > > order by v.sort, o.name > > > > > > This query will not run correctly .. if inventory is > 0 great, then we don't > > need to know about the backorder flag... but if inventory is 0 or less, then > > we want to see if the backorder flag is 1 or 0 .. if it is 1, then I want the > > value to show, but if it is 0, do not show the value. > > > > I know I could do this in CF with but would rather have it done in > > the query. > > > > The ending result needs to show the itemid, item, and price > > > > Any suggestions? > > > > TIA > > > > Paul Giesenhagen > > QuillDesign > > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT: SQL Query Depending on value
First thing I see, Paul is that you say "if inventory is 0 or below, then I need to check backorder value" But your SQL says: ... and inventory > 0 ... ??? Aren't these opposites? On 5/20/02 8:38 PM, "Paul Giesenhagen" <[EMAIL PROTECTED]> wrote: > I am writing a join query that displays information depending on a value, > > There are two columns that deal with inventory, they are > inventory = number in stock (could be a negative) > backorder = either a 1/0 do we allow backordering or not, if it is 1 Yes, we > allow backordering if 0 No we do not. > > The situation is, if inventory is 0 or below, then I need to check backorder > value to see if it is a 1 or zero. If it is a 1, then I want to display that > value, if it is a 0 and the inventory is 0 or below then do not show the > value. > > Here is what I have so far! > > > select o.type, v.id AS itemid, v.item, v.price > from #caller.prefs.option_table# o, #caller.prefs.option_values_table# v > where o.id = '#options.optionid#' > and o.id = v.optionid > and inventory > 0 > and backorder = 1 > order by v.sort, o.name > > > This query will not run correctly .. if inventory is > 0 great, then we don't > need to know about the backorder flag... but if inventory is 0 or less, then > we want to see if the backorder flag is 1 or 0 .. if it is 1, then I want the > value to show, but if it is 0, do not show the value. > > I know I could do this in CF with but would rather have it done in > the query. > > The ending result needs to show the itemid, item, and price > > Any suggestions? > > TIA > > Paul Giesenhagen > QuillDesign > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT SQL Query
Morning, Paul: Parent/child department relationships can be represented using only one table: Departments (DepartmentID, DepartmentIDP, Name) The DepartmentID field is set up as an identity field (SQL Server) or primary key (MS Access). The DepartmentIDP field, which represents a parent department, can be set up to allow nulls in SQL Server or as not required in MS Access. Your code to render a list of parent/child department links could be something like the following: SELECT DepartmentID,DepartmentIDP,Name FROM Departments WHERE DepartmentID = #URL.id# SELECT DepartmentIDP,Name FROM Departments WHERE DepartmentID = #ParentID# #aParents[j][2]#\ #GetDepartment.Name# SELECTDepartmentID,Name FROM Departments WHERE DepartmentIDP = #GetDepartment.DepartmentID# ORDER BY Name #GetChildDepts.Name# This is probably not be exactly what you're after but, hopefully, it will point you in the right direction. Enjoy your weekend ;) Dina - Original Message - From: Paul Giesenhagen To: CF-Talk Sent: Friday, April 12, 2002 10:51 PM Subject: OT SQL Query Ok, I have an operation that I need help writing a query for... it may be simple or it may be impossible... I will do my best to explain what I am wanting in advance, thanks for your help. I have a table: (owner and sub_object values are ID values in another table) idownersub_object 1 AB 2 AC 3 AD 4 BY 5 BJ 6 CK 7 KL 8 KF 9 YW This is a simple parent/child relationship, the owner is the parent and the sub_object is the child. Parents can be children and children can become Parents... These items are departments, Department A has a sub-departments of B, C & D AND Sub-department B (now a parent) has sub-departments - Y and J ...AND sub-department Y (now a parent) has a sub-department W ...and so on .. forever down and down A Known Factor: OWNER A is the starting point (that is known) so B,C,D are the MAIN departments I want to write a query or queries/outputs/loops whatever, that will display the following ... So I want the output to look like this: B B/Y B/Y/W B/J C C/K C/K/L C/K/F Side Note, each of the owner and sub_object values are defined in another table, so I will have to join two tables together to get the actual NAME of the department... I don't think that will matter but thought I should point it out. Any help would be appreciated! Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT SQL Query
CFX_MAKETREE. It does this without recursive inner joins on the database. http://devex.macromedia.com/developer/gallery/info.cfm?ID=CA34727A-2830-11D4 -AA9700508B94F380&method=Full - Original Message - From: "Paul Giesenhagen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, April 12, 2002 8:51 PM Subject: OT SQL Query > Ok, I have an operation that I need help writing a query for... it may be simple or it may be impossible... I will do my best to explain what I am wanting in advance, thanks for your help. > > I have a table: (owner and sub_object values are ID values in another table) > > idownersub_object > 1 AB > 2 AC > 3 AD > 4 BY > 5 BJ > 6 CK > 7 KL > 8 KF > 9 YW > > This is a simple parent/child relationship, the owner is the parent and the sub_object is the child. Parents can be children and children can become Parents... > > These items are departments, > > Department A has a sub-departments of B, C & D > AND > Sub-department B (now a parent) has sub-departments - Y and J > ...AND > sub-department Y (now a parent) has a sub-department W > ...and so on .. forever down and down > > A Known Factor: > OWNER A is the starting point (that is known) so B,C,D are the MAIN departments > > I want to write a query or queries/outputs/loops whatever, that will display the following ... So I want the output to look like this: > > B > B/Y > B/Y/W > B/J > C > C/K > C/K/L > C/K/F > > Side Note, each of the owner and sub_object values are defined in another table, so I will have to join two tables together to get the actual NAME of the department... I don't think that will matter but thought I should point it out. > > Any help would be appreciated! > > Paul Giesenhagen > QuillDesign > http://www.quilldesign.com > SiteDirector v2.0 - Commerce Builder > > > > > __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT: SQL Query Help
I'm using Aliases SELECT A.ResortName, B.Year, B.News FROM Resorts A, NewFor B WHERE B.ResortID = A.ResortID ORDER BY B.Year hehehehe >From: "Jim McAtee" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: OT: SQL Query Help >Date: Mon, 11 Jun 2001 13:16:05 -0600 > >I could use a bit of help with what I'm guessing is a fairly easy query. >I've got the following: > >Tables: >Resorts >NewFor > >Resorts Table: >ResortID pk >ResortName > >NewFor Table: >NewForID pk >ResortID fk >Year >News > >In the NewFor table I've got news for 1999, 2000, 2001, etc. for most (but >not all resorts). I want to return rows for all resorts, along with any >news there may be for the current year. > >So the output would be: >ResortName, News (for only 2001, which may be null) > >If I do a join, generally I'm left with only resorts which have news >records >for the current year, which isn't what I'm after. > >Thanks for any assistance, >Jim > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
