Yes, distinct or a group by.
This should work:
select *
from properties p
where p.mls_number not in ( select pc.mls_number from
properties_copy pc where pc.mls_number is not null and pc.mls='hmls' )
and p.mls = 'hmls'
and p.mls_number is not null
And if not this should:
SELECT pc.mls_number
FROM properties_copy pc LEFT OUTER JOIN
properties p ON pc.mls = p.mls
WHERE p.mls IS NULL
AND pc.mls = 'hmls'
GROUP BY mls_number, mls
On Mon, Oct 4, 2010 at 12:29 AM, Andrew Scott <[email protected]>wrote:
>
> You might need to provide the distinct, or adjust the join correctly. I am
> betting the distinct is needed.
>
>
> Regards,
> Andrew Scott
> http://www.andyscott.id.au/
>
>
> > -----Original Message-----
> > From: Rick Faircloth [mailto:[email protected]]
> > Sent: Monday, 4 October 2010 3:16 PM
> > To: cf-talk
> > Subject: RE: Can't figure out a query to accomplish this...
> >
> >
> > Well, when I run this:
> >
> > select *
> > from properties p
> > where p.mls_number not in ( select pc.mls_number from
> > properties_copy pc )
> > and p.mls = 'hmls'
> > and p.mls_number is not null
> >
> > I get no records returned...
> >
> >
> >
> > -----Original Message-----
> > From: Andrew Scott [mailto:[email protected]]
> > Sent: Sunday, October 03, 2010 11:36 PM
> > To: cf-talk
> > Subject: RE: Can't figure out a query to accomplish this...
> >
> >
> > I was wondering why you are doing an left join and not a sub select, the
> > join will create duplicates if not the right way around.
> >
> > Regards,
> > Andrew Scott
> > http://www.andyscott.id.au/
> >
> >
> >
> > > -----Original Message-----
> > > From: Rick Faircloth [mailto:[email protected]]
> > > Sent: Monday, 4 October 2010 2:28 PM
> > > To: cf-talk
> > > Subject: RE: Can't figure out a query to accomplish this...
> > >
> > >
> > > Spoke (wrote) too soon...
> > >
> > > I'm getting the correct records, but I just realized I'm getting two of
> > every
> > > field returned. I tried other joins, but can't affect the fields so
> that
> > I get just
> > > one field.
> > >
> > > How do I modify the query to return just one field?
> > >
> > > I'm getting:
> > >
> > > (record 1) area area bedrooms bedrooms bathrooms bathrooms, etc...
> > >
> > > When it should be:
> > >
> > > (record 1) area bedrooms bathrooms, etc...
> > >
> > > ???
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Rick Faircloth [mailto:[email protected]]
> > > Sent: Sunday, October 03, 2010 11:17 PM
> > > To: cf-talk
> > > Subject: RE: Can't figure out a query to accomplish this...
> > >
> > >
> > > Ok...here's the final solution.
> > > (Had to watch some football before I could sort it out :o)
> > >
> > > select p.mls_number
> > > from properties p
> > > left join properties_copy pc
> > > on pc.mls_number = p.mls_number
> > > where pc.mls_number is null
> > > and p.mls = 'hmls'
> > > and p.mls_number is not null
> > >
> > > That last line had to be added because there were some
> > > records with no mls_number that I didn't know about earlier.
> > >
> > > I swear it seems like this was one of the first queries
> > > I tried. But, whatever, it works!
> > >
> > > Thanks for the help, everyone!
> > >
> > > Rick
> > >
> > > -----Original Message-----
> > > From: Jason Fisher [mailto:[email protected]]
> > > Sent: Sunday, October 03, 2010 7:07 PM
> > > To: cf-talk
> > > Subject: Re: Can't figure out a query to accomplish this...
> > >
> > >
> > > Ah, then reverse the JOIN (unless MySQL handles this differently) if
> > > you want only the ones IN _copy:
> > >
> > > SELECT pc.mls_number
> > > FROM properties_copy pc LEFT OUTER JOIN
> > > properties p ON pc.mls = p.mls
> > > WHERE p.mls IS NULL
> > > AND pc.mls = 'hmls'
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > ~~~~~~~~~~~|
> > > Order the Adobe Coldfusion Anthology now!
> > > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-
> > > Dinowitz/dp/1430272155/?tag=houseoffusion
> > > Archive: http://www.houseoffusion.com/groups/cf-
> > > talk/message.cfm/messageid:337817
> > > Subscription: http://www.houseoffusion.com/groups/cf-
> > talk/subscribe.cfm
> > > Unsubscribe: http://www.houseoffusion.com/groups/cf-
> > > talk/unsubscribe.cfm
> >
> >
> >
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > ~~~~~~~~~~~|
> > Order the Adobe Coldfusion Anthology now!
> > http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-
> > Dinowitz/dp/1430272155/?tag=houseoffusion
> > Archive: http://www.houseoffusion.com/groups/cf-
> > talk/message.cfm/messageid:337819
> > Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
> > Unsubscribe: http://www.houseoffusion.com/groups/cf-
> > talk/unsubscribe.cfm
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337821
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
