It's confusing to see what you're trying to acheive because you can't tell from the query code you posted which fields refer to which table. It would help if you assigned table aliases, e.g.
SELECT a.field, b.field FROM tableA a, tableB b and posted the code again. Beyond that, maybe UNION would do the trick? <cfquery name="commercial" datasource="remotename"> SELECT mls, type, asking_price, address, city, state, zip FROM Commercial WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' UNION SELECT mls, type, asking_price, address, city, state, zip FROM Multi_Family WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' UNION SELECT mls, type, asking_price, address, city, state, zip FROM Land WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' </cfquery> UNION cobbles all SELECT statements together, as long as each has the same fields. Check documentation depending on your DB. HTH, Gyrus [EMAIL PROTECTED] work: http://www.tengai.co.uk play: http://www.norlonto.net PGP key available ----- Original Message ----- From: "Chris Edwards" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 28, 2002 6:08 PM Subject: db query problem > Hi > > I need to condense these queries into one, can anyone help? > > <cfquery name="residential" datasource="remotename"> > SELECT mls, type, asking_price, address, city, state, zip > FROM Residential, Commercial, Multi_Family, Land > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > </cfquery> > > <cfquery name="commercial" datasource="remotename"> > SELECT mls, type, asking_price, address, city, state, zip > FROM Commercial > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > </cfquery> > > <cfquery name="multi" datasource="remotename"> > SELECT mls, type, asking_price, address, city, state, zip > FROM Multi_Family > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > </cfquery> > > <cfquery name="land" datasource="remotename"> > SELECT mls, type, asking_price, address, city, state, zip > FROM Land > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > </cfquery> > > -- > Chris Edwards > Web Application Developer > Outer Banks Internet, Inc. > 252-441-6698 > [EMAIL PROTECTED] > http://www.OuterBanksInternet.com > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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
