mysql query is producing more results than it should

Question

SELECT S.CLIENT,S.IP_DOMAIN as IP, IFNULL(K.DATE, DATE '0000-00-00') AS RecentDate
      FROM PLD_SERVERS AS S JOIN PLD_SEARCHES AS K ON S.ID = K.SERVER_ID

This query will produce as many results as entries in the PLD_SEARCHES. For example:

I have 3 entries in PLD_SERVERS and 18 entries in PLD_SEARCHES. The output of this query will be 18 but i need it to be 3 (as the number of PLD_SERVERS entries) with the recent date as a join field from PLD_SEARCHES.

Answer 1

Try using a left join:

   SELECT  S.CLIENT,S.IP_DOMAIN as IP, IFNULL(K.DATE, DATE '0000-00-00') AS RecentDate
     FROM  PLD_SERVERS AS S 
LEFT JOIN  PLD_SEARCHES AS K ON S.ID = K.SERVER

The problem probably originates from an incorrect JOIN type for this situation. Check the different types of join:

1. http://en.wikipedia.org/wiki/Join_%28SQL%29
2. http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

Answer 2

How about something like:

SELECT S.CLIENT,S.IP_DOMAIN as IP
    , IFNULL(
        (
        Select Max(K2.DATE)
        From PLD_SEARCHES AS K1 
        Where S.ID = K1.SERVER_ID
        ), DATE, '0000-00-00') AS RecentDate
FROM PLD_SERVERS AS S 
Where Exists(   
            Select 1
            From PLD_SEARCHES AS K1 
            Where S.ID = K1.SERVER_ID
            )

Here I'm using the Exists function to determine which rows to show and then I use a second subquery to find the last date.