Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filter Out Duplicate Rental Listings From DB 2

Status
Not open for further replies.
3dColor

3dColor

Programmer
Jan 10, 2006
240
US
Code: 
SELECT listing.rentaladtitle
		 , images.tnName
		 , listing.listingId
		 , listing.rentpermonth
		 , listing.bedrooms
		 , listing.bathrooms
		 , listing.city
		 , listing.address
		 , listing.latitude
		 , listing.longitude
		 , listing.phoneAd
		 , listing.nameContactAd
		 , LEFT(listing.rentaladdescription, 10)
					 AS rentaladdescription
	   FROM listing
	   	INNER 
		  JOIN listingparameter 
			ON listingparameter.listingFk = listing.listingId 
		   AND listingparameter.offline = 1 
	LEFT OUTER
	  JOIN ( SELECT listingFk
				  , MIN(imageOrder) AS min_order
			   FROM images
			 GROUP
				 BY listingFk ) AS m
		ON m.listingFk = listing.listingId
	LEFT OUTER
	  JOIN images
		ON images.listingFk = listing.listingId
	   AND images.imageOrder = m.min_order
	WHERE 	nameContactAd = #pmLowerCaseWithoutDashes#'

If I have two of the exact same "listing.address" how can I just return one unique property?

Example:
listing.address = 123 Any Street
listing.address = 123 Any Street
 
Sort by date Sort by votes
I tried that but I got an error.

Code: 
SELECT listing.rentaladtitle
     , images.tnName
     , listing.listingId
     , listing.pmFk
     , listing.proptype
     , listing.rentpermonth
     , listing.squarefeet
     , listing.bedrooms
     , listing.bathrooms
     , listing.city
     , MAX(listing.address) AS max_address
     , listing.nameContactAd
     , listingparameter.planType
     , listingparameter.listingBeginDate
     , LEFT(listing.rentaladdescription, 275) 
          AS rentaladdescription
     , CASE WHEN listingparameter.plantype = 'Premium' 
            THEN 0 ELSE 1 END
          AS listing_sortorder
  FROM listing 
	INNER 
	  JOIN listingparameter 
		ON listingparameter.listingFk = listing.listingId 
	   AND listingparameter.offline = 1 
           AND listing.address = max_address
	LEFT OUTER
	  JOIN ( SELECT listingFk                   
				  , MIN(imageOrder) AS min_order
			   FROM images                      
			 GROUP                              
				 BY listingFk ) AS m
		ON m.listingFk = listing.listingId
	LEFT OUTER
	  JOIN images
		ON images.listingFk = listing.listingId
	   AND images.imageOrder = m.min_order    
	 WHERE nameContactAd = <cfqueryparam value='#pmLowerCaseWithoutDashes#' cfsqltype="cf_sql_varchar">
	ORDER 
		BY listing_sortorder
		 , listingparameter.listingBeginDate DESC
		 , listing.listingId DESC

I am over my head on this one, I am not sure how to put this together correctly.
 
Upvote 0 Downvote
can't use MAX or MIN without a GROUP BY

try this --
Code: 
SELECT youneek.rentaladtitle
     , images.tnName
     , youneek.listingId
     , youneek.rentpermonth
     , youneek.bedrooms
     , youneek.bathrooms
     , youneek.city
     , youneek.address
     , youneek.latitude
     , youneek.longitude
     , youneek.phoneAd
     , youneek.nameContactAd
     , youneek.rentaladdescription
  FROM ( SELECT address
              , MIN(rentaladtitle) AS rentaladtitle 
              , MIN(listingId)     AS listingId     
              , MIN(rentpermonth)  AS rentpermonth  
              , MIN(bedrooms)      AS bedrooms      
              , MIN(bathrooms)     AS bathrooms     
              , MIN(city)          AS city          
              , MIN(address)       AS address       
              , MIN(latitude)      AS latitude      
              , MIN(longitude)     AS longitude     
              , MIN(phoneAd)       AS phoneAd       
              , MIN(nameContactAd) AS nameContactAd 
              , MIN(LEFT(rentaladdescription,10))
                          AS rentaladdescription
           FROM listing
         GROUP
             BY address ) AS youneek
INNER
  JOIN listingparameter
    ON listingparameter.listingFk = listing.listingId
   AND listingparameter.offline = 1
LEFT OUTER
  JOIN ( SELECT listingFk
              , MIN(imageOrder) AS min_order
           FROM images
         GROUP
             BY listingFk ) AS m
    ON m.listingFk = listing.listingId
LEFT OUTER
  JOIN images
    ON images.listingFk = listing.listingId
   AND images.imageOrder = m.min_order
 WHERE nameContactAd = #pmLowerCaseWithoutDashes#

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Hi Rudy,

Thanks for helping me out! I tried your suggestion and I got the following error:

CF Error: Error Executing Database Query.
Duplicate column name 'address'
Click to expand...

Any other ideas?

Thanks!
Dave
 
Upvote 0 Downvote
you have address in more than one table so you have to reference it by the tablename and then column name i.e.
table1.address for example.
 
Upvote 0 Downvote
Quelphdad,

I looked at all my tables and only the listing table had a column called address.

But I think you are headed in the right direction so I tried this - but it didn't work:

Code: 
	SELECT youneek.rentaladtitle
     , images.tnName
     , youneek.listingId
     , youneek.rentpermonth
     , youneek.bedrooms
     , youneek.bathrooms
     , youneek.city
     , youneek.address
     , youneek.latitude
     , youneek.longitude
     , youneek.phoneAd
     , youneek.nameContactAd
     , youneek.rentaladdescription
  FROM ( SELECT listing.address
              , MIN(rentaladtitle) AS rentaladtitle
              , MIN(listingId)     AS listingId     
              , MIN(rentpermonth)  AS rentpermonth  
              , MIN(bedrooms)      AS bedrooms      
              , MIN(bathrooms)     AS bathrooms     
              , MIN(city)          AS city          
              , MIN(listing.address) AS address       
              , MIN(latitude)      AS latitude      
              , MIN(longitude)     AS longitude     
              , MIN(phoneAd)       AS phoneAd       
              , MIN(nameContactAd) AS nameContactAd
              , MIN(LEFT(rentaladdescription,10))
                          AS rentaladdescription
           FROM listing
         GROUP
             BY listing.address ) AS youneek
INNER
  JOIN listingparameter
    ON listingparameter.listingFk = listing.listingId
   AND listingparameter.offline = 1
LEFT OUTER
  JOIN ( SELECT listingFk
              , MIN(imageOrder) AS min_order
           FROM images
         GROUP
             BY listingFk ) AS m
    ON m.listingFk = listing.listingId
LEFT OUTER
  JOIN images
    ON images.listingFk = listing.listingId
   AND images.imageOrder = m.min_order
 WHERE nameContactAd = #pmLowerCaseWithoutDashes#
 
Upvote 0 Downvote
Rudy!

Thanks!!! I really appreciate it. It works great now (I had to make a few other changes to make it work, but what's cool is I actually fixed the other items myself).

One question remains however: What is "youneek", is that another way of writing "unique" but you can't write unique because it is a reserved word in MySQL?

Dave
 
Upvote 0 Downvote
Rudy,

I started testing the query that I thought worked fine but I got too much filtering as I began to test it further.


In the link above is the query dump and you will see 4 results when there should be 7 unique rentals.

I initially thought that some other column in the 'listing' table was duplicated thus creating more duplication filtering but on the 3 missing rentals I could not locate a pattern.

I am a little perplexed. Can you offer any advice on what I could try to test this further to see why the 3 are not showing up in the dump?
 
Upvote 0 Downvote
Can you offer any advice on what I could try to test this further to see why the 3 are not showing up in the dump?de-unique-ify the query and look at the 7 rows

you'll see the duplication in the address, because that's the column we unique-ified

by the way, you started this thread with...
If I have two of the exact same "listing.address" how can I just return one unique property?

Example:
listing.address = 123 Any Street
listing.address = 123 Any Street
Click to expand...
so all we did was address (no pun intended) that problem without any regard whatsoever that these two addresses might be from different listings or even different cities

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Update column based on input from another column 3
Replies
6
Views
404
penguinspeaks
penguinspeaks
wilsjay22
  • Locked
  • Question
Freeing up disk space from information_schema
Replies
1
Views
315
jg2828
jg2828
trandrus
  • Locked
  • Question
incrementing a column without having to query the db first
Replies
4
Views
170
Chris Miller
Chris Miller
PeDa
  • Locked
  • Question
Opening MySQL table from Microsoft Access
Replies
1
Views
186
PeDa
PeDa
martinkaeser
  • Locked
  • Question
MySQL Query for incrementing field from duplicates in database
Replies
4
Views
168
martinkaeser
martinkaeser

Part and Inventory Search

Sponsor

Back
Top