Variables to UNION clause???

Hi Guys,

I have a series of tables for classified ads.. Each core category has it's own table. Since each category's actual listing page requires a different URL, I'm having trouble trying to figure out how to include different URLs when I list the results of a Union query.

Because I want ALL classifieds, when the user is viewing their listings, to be combined into a single record set, I am going to do a Select/UNION ALL


<CFQUERY NAME="all_listings" datasource="classifieds">

SELECT
id as classified_id,
title as classified_title,
created as classified_created,
category_a as classified_category_a

FROM announcement_classifieds

UNION ALL

SELECT
id as classified_id,
title as classified_title,
created as classified_created,
category_a as classified_category_a

FROM aircraft_classifieds

WHERE member_id = '#MYID#'
;

</CFQUERY>


When the query results are displayed, the Title column will include the results as links, so that the user can click on the title to see the actual classified ad. While part of the query returns the unique ID for each classified ad, the ID is only unique to that table.

Is there a way I can include an attribute to each record in the query result that differentiats which table it came from?

Again the reason is the URL to view each classified ad is slightly different for each main category.

ex. company.com/view_aircraft_ad.cfm?id=1232
company.com/view_apartment_ad.cfm?id=1232


I want to be able to include the URL like company.com/view_#category#_ad.cfm?id=1232 and depending on which table the record came from, declare the category.

Thus you will get an extra column named whichtable in the result.