Remove Duplicates From Result Of Select On Two Tables

I cannot figure this one out.

I have two tables. One table contains product recent entries. The other contains the information (name, address, etc.) The common field is the ID (not the row ID).

What I want to do is to query the recent entries table for the 10 most recent entries then join it to the information table so I can return the info with the recent entries.

The problem is when there are recent entries with duplicate product IDs. That in itself is OK. What I do not want is the redundant rows appearing in the query result. I do not want the query to return more than one row of the same product. I cannot use DISTINCT, as that will not select anything in the recent entries table with multiple rows having the same ID. I tried GROUP by, but that eliminated anything with a duplicate ID in the results altogether.

SELECT t2.name, t2.city, t2.state, t2.id, t2.slug
FROM recent_entries_table AS t1, info_table AS t2
WHERE approved=1 AND t2.id=t1.id
ORDER BY entry_time DESC
LIMIT 10  

I hope this makes sense. If the 10 most recent entries in the recent entries table all have the same product ID, I do not need to display 10 lines of recent products all of the same thing. I want the 10 most recent entries that are not duplicates of each other.

I thought a GROUP BY would work, but it doesn’t. I also tried using subqueries. It does not work. I got a message that one subquery returned more than one row when I set the WHERE equal to the subquery using the equal sign. Then I got an error message saying my version of MYSQL (only 1 year old) does not support using IN/ANY/other stuff using a LIMIT.

Thanks for any help. :slight_smile:

SELECT t2.name
     , t2.city
     , t2.state
     , t2.id
     , t2.slug
  FROM ( SELECT id 
              , MAX(entry_time) AS latest_time
           FROM recent_entries_table
         GROUP
             BY id ) AS t1
INNER
  JOIN info_table AS t2
    ON t2.id = t1.id
 WHERE t2.approved = 1
ORDER 
    BY t1.latest_time DESC LIMIT 10

Thanks a lot for the reply. Your example did not work as written. I had to experiment and tweak it. But I got it to work. The problem was an “Unknown Column” error with the line “WHERE t2.approved = 1”. I thought it should be t1.approved since t1 is the latest entries table and that is the only table with an “approved” column. But that still returned an “unknown column” error. So what I did is removed the table alias and stuck the approved=1 up inside the nested select like this:


SELECT t2.name
     , t2.city
     , t2.state
     , t2.id
     , t2.slug
  FROM ( SELECT id 
              , MAX(entry_time) AS latest_time
           FROM recent_entries_table
         WHERE approved=1
          GROUP
             BY id ) AS t1
INNER
  JOIN info_table AS t2
    ON t2.id = t1.id
ORDER 
    BY t1.latest_time DESC LIMIT 10


I tested it out about and it seems to work just fine. :slight_smile: I don’t see why it wouldn’t. I’m not exactly sure why I need the “MAX(entry_time) AS latest_time” part. I tried taking it out and things didn’t seem to work right. I guess I’ll leave it in because it creates an alias to be used later during the ORDER BY statement.

Thanks for pointing me in the right direction. :slight_smile: