2 options off the top of my head: 1) have your query return as many records as there are, but limit the output of the query by using MAXROWS attribute of <cfoutput> tag
2) join your first table to a sub-query that pulls only one record from the other table in your sql: SELECT TABLE1.*, sq.PictureID, sq.PictureName FROM TABLE1 JOIN (SELECT TOP 1 AlbumID, PictureID, PictureName FROM TABLE2 WHERE AlbumID = 1) AS sq ON TABLE1.AlbumID = sq.AlbumID WHERE TABLE1.AlbumID = 1 depending on your db, use either TOP 1 as above, or use LIMIT clause (i.e. in MySQL) Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 29/05/2009 21:41, Chad Gray wrote: > I have two tables that are joined one to many. > > I would like to write a query to get the one from the first table and only > one from the many table. > > TABLE1 > AlbumID, AlbumTitle > 1, MyPictures > > TABLE2 > PictureID, AlbumID, PictureName > 1, 1, test.jpg > 2, 1, test2.jpg > > How do I write the query to only get one record from TABLE2 when I join? > > SELECT * > FROM TABLE1 > JOIN TABLE2 ON TABLE2.AlbumID = TABLE1.AlbumID > WHERE TABLE1.AlbumID = 1 > > This query returns 2 records because there are two matches in TABLE2. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322969 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
