Skip to main content
A
Anonymous
October 30, 2008
Question

Query of Query Neccessary?

  • October 30, 2008
  • 3 replies
  • 1078 views
Guess I'm losing it.

I have a table of locations - key field = location_ID.

Another (lookup) table has fire code violations - a location can have no or many violations (many to one).

FKey = violation_location_ID

I simply want to display a locations in rows with, among other data, a simple yes or no (have or have not had a violation)

This gets all locations:

<cfquery name="get_location" datasource="#Request.BaseDSN#">
SELECT location_ID, location_name, location_num, location_street, alarm_test_date, sprinkler_test_date, suppression_test_date
FROM location_main
ORDER BY location_name
</cfquery>

This gets only locations that have had violations:

<cfquery name="get_violation" datasource="#Request.BaseDSN#">
SELECT location_ID, violation_location_ID
FROM location_main, lookup_violation
WHERE location_ID = violation_location_ID
</cfquery>

Would I use the outputs of both queries in another query to determine yes or no for each returned record?

Thanks for helping get my head back on straight...

newportri
This topic has been closed for replies.

3 replies

D
Dan_Bracuk
Inspiring
October 31, 2008
syntax varies with the db, but try this concept


SELECT m.location_ID, v.violation_location_ID, 'yes' text, 'black' colour,
COUNT (v.violation_location_ID) AS TotalViolations
FROM location_main m
JOIN lookup_violation v
ON m.location_ID = v.violation_location_ID

GROUP BY m.location_ID, violation_location_ID, text, colour

union

select location_id, 0 violation_location_id, 'no' text, 'red' colour, 0 totalviolations
from
location_main
where location_id in
(select location_id from location_main
except
select distinct location_main
from lookup_violation)

D
Dan_Bracuk
Inspiring
October 31, 2008
Take away the keyword "as" from the from clause of your query. It only get used in the select clause.
A
Anonymous
October 31, 2008
Dan:

I figured out that this:

<cfquery name="get_count" datasource="#Request.BaseDSN#">

SELECT m.location_ID, v.violation_location_ID,
COUNT (v.violation_location_ID) AS TotalViolations
FROM location_main m
LEFT JOIN lookup_violation v
ON m.location_ID = v.violation_location_ID
GROUP BY m.location_ID, violation_location_ID
</cfquery>

<cfoutput query="get_count">#YesNoFormat(get_count.TotalViolations)#</cfoutput>

works- I'm getting cartesian product - 1600 Yes-Nos- I'll figure out how to calm that down-

Thanks cfSearching and DB.
A
Anonymous
October 31, 2008
CF & DB:

The attached code renders "Yes" in the Violation /Yes/No column of all records?
-
-__cfSearching__-
Inspiring
October 30, 2008
Try using an OUTER join so your query pulls all locations. In the SELECT list, COUNT the violation id's.

SELECT
m.location_ID,
m.violation_location_ID,
COUNT(v.violation_location_ID) AS TotalViolations

FROM location_main as m LEFT JOIN lookup_violation as v ON m.location_ID = v.violation_location_ID
GROUP BY m.location_ID, violation_location_ID


ColdFusion should be able to convert the count to a boolean. (ie No - if the count is zero, Yes - if count is <> 0)

#YesNoFormat(yourQuery.TotalViolations)#

A
Anonymous
October 31, 2008
CF:
Thanks for your help.

I tried this:

<cfquery name="get_count" datasource="#Request.BaseDSN#">

SELECT m.location_ID, m.violation_location_ID,
COUNT (v.violation_location_ID) AS TotalViolations
FROM location_main as m LEFT JOIN lookup_violation as v
ON m.location_ID = v.violation_location_ID
GROUP BY m.location_ID, violation_location_ID
</cfquery>

<cfoutput>#YesNoFormat(get_count.TotalViolations)#</cfoutput>

Error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Thanks again,

newportri
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices