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?
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?
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?
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?
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?
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?
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
How to write this query?
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:303243 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
