EasyCFM.COM ColdFusion Forums / MS Access Related Issues / combining 2 queries into one

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: combining 2 queries into one

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

rickaclark
11-25-2008 @ 6:19 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

I have two separate queries that I would like to combine into one. Unfortunately, I can't use join, due to one query has a where statement and the other doesn't. Here is my code:

<cfif #page_name# IS ("Friday Party")>
<cfquery name="list" datasource="#application.main#">
select performer, bio, timeStart, timeEnd, dance, filename
from performers
where dance = 'yes'
UNION
select performer, bio, timeStart, timeEnd, dance, filename
from danceInfo
order by performer asc
</cfquery>

However, this code does not work. Does anyone have a solution that works?



rickaclark
11-26-2008 @ 5:27 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

I got the above code to somewhat work. When I use the union command it restricts the bio field to appr 255 characters. The bio field is set to memo, so in both tables. If I take one of the queries out then the text flows properly.

What gives?

Scarecrow
01-07-2009 @ 6:22 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Why can't you use a join ?
I assume because you want all the records from danceInfo ?


As long as the tables have a key that they can be joined on you can

select b.performer, b.bio, b.timeStart, b.timeEnd, b.dance, b.filename
,p.performer, p.bio, p.timeStart, p.timeEnd, p.dance, p.filename
from performers p right outer join  danceInfo d On p.performer = d.performer
where p.dance = 'yes'
order by p.performer asc

Ken


www.scarecrowapplications.com

Webmaster
01-07-2009 @ 8:51 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

There is a tutorial for this here:

http://tutorial98.easycfm.com/

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

\m/ (>.<) \m/
--- rock on ---


Website Designed and Developed by Pablo Varando.