RE: How to write this query?
Thanks for the reply, Azadi. However, your solution has the same problem I encountered with a regular JOIN statement: the limit 1 part of the interior select statement limited my results to only 1 property with 1 photo, instead of all properties, but only 1 photo each. I tried: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price from smlc_acr sa inner join select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap on sa.mls_number = sap.photo_mls_number order by list_price but that gives me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap ' at line 4 I can't see the error in my syntax. But then, again, I'm not the most experienced join writer, having avoided them when I could. Thanks, again. Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, April 13, 2008 1:45 AM To: CF-Talk Subject: Re: How to write this query? something like the following (note: mysql syntax): SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, SAP_SQ.photo_filename FROM smlc_acr sa INNER JOIN (SELECT photo_filename, photo_mls_number FROM smlc_acr_photos sap WHERE photo_filename '' LIMIT 1) SAP_SQ ON sa.mls_number = SAP_SQ.photo_mls_number ORDER BY sa.list_price Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303248 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How to write this query?
Rick, You can use FROM and immediately use ON. That's why you have a syntax error. ON is used with joins. Ravi. On Sun, Apr 13, 2008 at 7:23 AM, Rick Faircloth [EMAIL PROTECTED] wrote: Thanks for the reply, Azadi. However, your solution has the same problem I encountered with a regular JOIN statement: the limit 1 part of the interior select statement limited my results to only 1 property with 1 photo, instead of all properties, but only 1 photo each. I tried: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price from smlc_acr sa inner join select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap on sa.mls_number = sap.photo_mls_number order by list_price but that gives me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap ' at line 4 I can't see the error in my syntax. But then, again, I'm not the most experienced join writer, having avoided them when I could. Thanks, again. Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, April 13, 2008 1:45 AM To: CF-Talk Subject: Re: How to write this query? something like the following (note: mysql syntax): SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, SAP_SQ.photo_filename FROM smlc_acr sa INNER JOIN (SELECT photo_filename, photo_mls_number FROM smlc_acr_photos sap WHERE photo_filename '' LIMIT 1) SAP_SQ ON sa.mls_number = SAP_SQ.photo_mls_number ORDER BY sa.list_price Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303249 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: How to write this query?
I meant to say can not Ravi. On Sun, Apr 13, 2008 at 7:30 AM, [STS] Ravi Gehlot [EMAIL PROTECTED] wrote: Rick, You can use FROM and immediately use ON. That's why you have a syntax error. ON is used with joins. Ravi. On Sun, Apr 13, 2008 at 7:23 AM, Rick Faircloth [EMAIL PROTECTED] wrote: Thanks for the reply, Azadi. However, your solution has the same problem I encountered with a regular JOIN statement: the limit 1 part of the interior select statement limited my results to only 1 property with 1 photo, instead of all properties, but only 1 photo each. I tried: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price from smlc_acr sa inner join select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap on sa.mls_number = sap.photo_mls_number order by list_price but that gives me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sap.photo_filename, sap.photo_mls_number from smlc_acr_photos sap ' at line 4 I can't see the error in my syntax. But then, again, I'm not the most experienced join writer, having avoided them when I could. Thanks, again. Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, April 13, 2008 1:45 AM To: CF-Talk Subject: Re: How to write this query? something like the following (note: mysql syntax): SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, SAP_SQ.photo_filename FROM smlc_acr sa INNER JOIN (SELECT photo_filename, photo_mls_number FROM smlc_acr_photos sap WHERE photo_filename '' LIMIT 1) SAP_SQ ON sa.mls_number = SAP_SQ.photo_mls_number ORDER BY sa.list_price Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: How to write this query?
How about something like: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price, (select top 1 sap.photo_filename from smlc_acr_photos sap where sap.photo_mls_number = sa.mls_number) as photo_filename from smlc_acr sa order by sa.list_price The only thing you may want to consider here is to include an order by on the subquery so that you get the first photo you want. Cheers, Kris What I want to do is get each property and the first photo for that property. Here's what I've tried: select distinct sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, sap.photo_filename from smlc_acr sa, smlc_acr_photos sap where sa.mls_number = sap.photo_mls_number order by list_price That doesn't work, because each row is distinct because of the new photo. I tried joins, but couldn't get that to work. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303253 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: How to write this query?
On Sun, Apr 13, 2008 at 8:27 AM, Kris Jones [EMAIL PROTECTED] wrote: How about something like: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price, (select top 1 sap.photo_filename from smlc_acr_photos sap where sap.photo_mls_number = sa.mls_number) as photo_filename from smlc_acr sa order by sa.list_price The only thing you may want to consider here is to include an order by on the subquery so that you get the first photo you want. If you have a unique and predictable (1,2,3) order by field, you could also do something like: SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price,p.photo_filename FROM smlc_acr sa LEFT JOIN smlc_acr_photos p ON p.photo_mls_number = sa.mls_number AND p.sortorder = 1 ORDER BY sa.list_price OR SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price,p.photo_filename FROM smlc_acr sa LEFT JOIN (SELECT photo_mls_number,photo_filename FROM smlc_acr_photos WHERE sortorder = 1) p ON p.photo_mls_number = sa.mls_number ORDER BY sa.list_price ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303255 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How to write this query?
Hi, Jim, et al... No sort order field, but this solution is working well: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, (select sap.photo_filename from smlc_acr_photos sap where sap.photo_mls_number = sa.mls_number limit 1) as photo_filename from smlc_acr sa order by sa.list_price Thanks everyone for the help! Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Sunday, April 13, 2008 10:42 AM To: CF-Talk Subject: Re: How to write this query? On Sun, Apr 13, 2008 at 8:27 AM, Kris Jones [EMAIL PROTECTED] wrote: How about something like: select sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price, (select top 1 sap.photo_filename from smlc_acr_photos sap where sap.photo_mls_number = sa.mls_number) as photo_filename from smlc_acr sa order by sa.list_price The only thing you may want to consider here is to include an order by on the subquery so that you get the first photo you want. If you have a unique and predictable (1,2,3) order by field, you could also do something like: SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price,p.photo_filename FROM smlc_acr sa LEFT JOIN smlc_acr_photos p ON p.photo_mls_number = sa.mls_number AND p.sortorder = 1 ORDER BY sa.list_price OR SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks03, sa.list_price,p.photo_filename FROM smlc_acr sa LEFT JOIN (SELECT photo_mls_number,photo_filename FROM smlc_acr_photos WHERE sortorder = 1) p ON p.photo_mls_number = sa.mls_number ORDER BY sa.list_price ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303261 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How to write this query?
something like the following (note: mysql syntax): SELECT sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, SAP_SQ.photo_filename FROM smlc_acr sa INNER JOIN (SELECT photo_filename, photo_mls_number FROM smlc_acr_photos sap WHERE photo_filename '' LIMIT 1) SAP_SQ ON sa.mls_number = SAP_SQ.photo_mls_number ORDER BY sa.list_price Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Rick Faircloth wrote: Hi, all... I've got two tables: 1 - list of properties 2 - photos for those properties There is more than one photo per property. What I want to do is get each property and the first photo for that property. Here's what I've tried: select distinct sa.street_number, sa.street_name, sa.city, sa.public_remarks_01, sa.public_remarks_02, sa.public_remarks_03, sa.list_price, sap.photo_filename from smlc_acr sa, smlc_acr_photos sap where sa.mls_number = sap.photo_mls_number order by list_price That doesn't work, because each row is distinct because of the new photo. I tried joins, but couldn't get that to work. Suggestions? Thanks! Rick ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303247 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
