Will ListFind() work, or should I go with something else? 1

[CryoGen]

I have two tables, one that stores specific date report periods, and another that tracks events associated with those periods.

The MySQL tables are:

REPORT
report_id  INT(8) PK
period     VARCHAR(25)

EVENT
event_id   INT(8) PK
report_id  INT(8)
event      LONGTEXT

When EVENT records are created, a corresponding report_id is stored in the EVENT table for each record. There can be any number of events created that tie back to one or more REPORT periods. What I want to be able to do is run a query on REPORT and use the results to query all the associated rows in the EVENTS table. Again, I'm using MySQL v3.23.

When I do a query on REPORT for, say, 'January 2004' it returns four records with report_ids numbered 1, 2, 3 and 4. From here I want to query the EVENT table using the four found report_ids ... something akin to ...

SELECT     report_id
FROM       events
WHERE      report_id = ANY RECORD IN EVENTS WITH THE SAME
                       REPORT_IDs AS THAT FOUND IN THE
                       REPORT TABLE.

I'm something of a neophyte at this, and this isn't hard to do when we're talking about finding a one-to-one match, but I'm not sure how to write the query when the values that need to be queried are more than one.

I'm wondering if ListFind() will help me out of the woods here. I hope this makes sense. Any help is appreciated.

 

[NorthStarDA]

i would use a sub-query or the #ValueList()# function, but unless i am mistaken, you can't do sub-queries in 3.23. So it might be like this

<cfquery name=query1>
SELECT report_id,period FROM reports WHERE (criteria)
</cfquery>

<cfquery name=query2>
SELECT report_id FROM events WHERE report_id IN (#ValueList(query1.report_id)#)
</cfquery>

im sure there is probably a better way to do it with a join, though- this is just my 2 cents.

 

[CryoGen]

OK, I used this code ...

<cfquery name="GetReportID" DATASOURCE="#REQUEST.DataSource#">
SELECT     report_id, period 
FROM       report
WHERE      period LIKE '#Form.month#'
AND        period LIKE '#Form.year#'
</cfquery>

<cfquery name="GetAssociatedEvents" DATASOURCE="#REQUEST.DataSource#">
SELECT    event_id, report_id, event_cat, task, status, org
FROM      events
WHERE     report_id IN (#ValueList(GetReportID.report_id)#)
</cfquery>

But it threw this error. The value list doesn't seem to be passing to the second query. Is there something wrong with my syntax?

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[MySQL][ODBC 3.51 Driver][mysqld-3.23.58-nt]You have an error in your SQL syntax near ')' at line 3

SQL = "SELECT event_id, report_id, event_cat, event, status, org FROM events WHERE report_id IN ()"

Data Source = "T3I"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:1) to (8:69) in the template file c:\inetpub\[URL unfurl="true"]wwwroot\t3i_public_new\news\submit\org_report_action.cfm.[/URL]

Date/Time: 04/23/04 10:18:48
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705)
Remote Address: 127.0.0.1

 

[NorthStarDA]

have you output the GetReportID query to see if it is returning any records?

#GetReportID.recordcount#

also, it seems that if you are using LIKE then you need to have % symbols in your criteria like so (i dont know your column type, this is just to give an idea)

<cfquery name="GetReportID" DATASOURCE="#REQUEST.DataSource#">
SELECT     report_id, period
FROM       report
WHERE      period LIKE '%#Form.month#%'
AND        period LIKE '%#Form.year#%'
</cfquery>

what exactly goes in report.period column? Is it an actual date value? I can see many ways this query would produce unexpected results as is, for instance:

if form.month = 04, then "LIKE '%#FORM.year#%' would return true if the YEAR value in period was 2004, regardless of month because 04 is in both.

 

[CryoGen]

Doh! The % symbols were the trouble -- it works great. Thanks North!

 

[r937]

is there some reason you want to do this in two separate queries?

it's a lot more efficient if you just use one query and allow the database to do what it's good at, joining rows from separate tables

select r.report_id
     , r.period 
     , e.event_id
     , e.event_cat
     , e.task
     , e.status
     , e.org
  from report
inner
  join events
    on r.report_id
     = e.report_id 
 where r.period like '%#form.month#%'
   and r.period like '%#form.year#%'

also, the LIKE method of finding the period does have a logic problem as NorthStarDA pointed out

rudy

http://r937.com/

SQL Consulting