Thanks Rodney. That got me started. Corrected the typos and what not and this is what works now.
SELECT Streets.Street_name + '@' + Streets_1.Street_name AS StreetName FROM Bus_Routes INNER JOIN Streets ON Bus_Routes.main_st_id = Streets.Street_id INNER JOIN Streets Streets_1 ON Bus_Routes.x_Street_id = Streets_1.Street_id WHERE Bus_Routes.Route_ID = 7 I was going backwards, starting at Bus_Routes rather than Streets. And I totally forget about aliasing table names and field names. Bruce ----- Original Message ---- From: "Bruce, Rodney S Mr CTR USA AMC" <[EMAIL PROTECTED]> To: SQL <[email protected]> Sent: Tuesday, September 11, 2007 2:01:32 PM Subject: RE: Can't get this query to work the way I want OOPs had a few of my misstypes in it still: Replace the Schule with Routes: Sorry about that SELECT Streets.Streetname AS MainStreet, Streets_1.Streetname AS CrossStreet FROM Routes INNER JOIN Streets ON Routes.main_st_id = Streets.St_id INNER JOIN Streets Streets_1 ON Routes.x_Street_id = Streets_1.Street_id -----Original Message----- From: Bruce Sorge [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 11, 2007 1:45 PM To: SQL Subject: Can't get this query to work the way I want Hello all, I have two tables that look like this: Routes table Schedule_ID School_ID Route_ID Main_St_ID X_Street_ID Side Pick_Up Drop_Off Streets table Street_ID Street_Name The Main and X street ID's in the Routes table are joined to the Street_ID field in Streets What I am trying to do is create a query so that I have select options that look like this: Baldy Mesa Rd @ Solano Rd Baldy Mesa Rd @ Goss Rd And so on. But when I create my query, I get no records returned if I join the Street_Id to the Main and X Street ID's in the Routes table. Do I need to do a sub query, or a query of queries in CF to make this work? I have tried usining AND, OR, Full Outer Join, Right Outer Join, Join and so on to no avail. Thanks, Bruce ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2951 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
