Query on Multiple Data Sources

[potinenip]

Hi,<br>I am trying to query from multiple data tables in a single CFQUERY statement. I want to display fields from 3 different tables and need the recordcount of such a query.<br>Can someone help me on this.<br>Thanks<br>Praveen<br>

 

[Janoh]

Please give an example:<br><br>A possible solution is to use joins but that's SQL stuff<br><br>Something like:<br><br>select t1.item1, t2.item2 , t3.item3<br>from t1,t2,t3<br>-----------------------------------<br>t1,t2,t3 are the 3 tables<br>but this join will return all the combinations between the 3 tables<br>Try at SQL forums or be a little more specific

 

[SnakeHollywood]

There must be a relationship between the tables if you want a single row to contain columns from different tables, otherwise you will just get repeated rows. For each row in table one, all rows will be selected in table2, and each row for table2, all rows in table3, so you get a lot of rows.<br><br>If for instance your 3 tables are <br>products<br>special_offers<br>vendors<br><br>all three table would have a related column called say PRODUCTID that relates to the product the record is for.<br>So you could do a query like this<br><br>SELECT products.product_name, products.price, special_offers.price, vendors.name<br>FROM products, special_offers, vendors<br>WHERE products.productID = 20<br>AND products.productID = special_offers.productID<br>AND products.productID = vendors.productID<br><br>This would then select only the product with the specified ID (20) and the vendor and special offer record with the matching ID.<br><br>To get a record count from the query you just use<br><br>queryname.recordcount<br><br> <p>Russ Michaels<br><a href=mailto:russ@satachi.com>russ@satachi.com</a><br><a href=

http://www.satachi.com>Satachi

Internet Development</a><br>For my personal book recommendations visit

http://www.satachi.com/books.html<br>

<br>

 

[potinenip]

Hi Russ,<br>Thanks for ur suggestion on this. I think it makes sense to use like that. My database is down now. I shall try this and let you know my position.<br>Thanks again.<br>Praveen<br><br>

 

[MarkCE]

I have a slightly more complex question on this theme.<br>Using CF and Access, I want to append the contents of a table in datasource A onto the end of an identical table in a different datasource B (a completely different database).<br>I suppose I could read into an array and then loop thru the array INSERTing VALUES into the new table, but is there a simpler way?<br>