Page 1 of 1

Select problems

Posted: Thu Aug 03, 2006 11:20 am
by shiznatix
I am running a big query where I do a bunch of left joins. It works fine but what I want is to be able to return rows ONLY if there is data in this one table that matches the main Id.

I have a user images table

Code: Select all

CREATE TABLE `listing_images` (
  `Id` int(10) NOT NULL auto_increment,
  `fkUserId` int(10) NOT NULL,
  `fkListingId` int(10) NOT NULL,
  `Path` varchar(255) NOT NULL,
  `isThumb` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
and I am searching on what is there the 'fkListingId'. So basically when I run the search I only want a row to be returned if any only if there is a record for that Id in the listing_images table.

I tried LEFT JOIN but since there can be like 10 rows in the listing_images table for each listing then I would get each listing returned to me 10 times which is not gonna work as it has to just return each listing once.

I don't really know how to go about this so any help is appreciated

Posted: Thu Aug 03, 2006 11:30 am
by feyd
INNER JOINs will only return rows when there is data found to match the constraint.

Posted: Thu Aug 03, 2006 11:30 am
by GM
With LEFT joins, the order of the tables is important - it returns only the rows from the first table, which match the rows from the second. You seem to be asking that it returns only one row from the first if it finds a row in the second?

Don't think that's possible - how does the DBMS know which row from the first table to return? Either way, if there exist many rows that satisfy the conditions, they will all be returned.

Maybe if you could post your query (I know it's a long query, but go for it anyway) I could understand what you are doing a bit better...

cheers, GM

Posted: Thu Aug 03, 2006 12:04 pm
by shiznatix
I will take a look at inner joins. here is the query right now:

Code: Select all

SELECT
        l.Id, l.AddedDate, l.AdminCheck, l.AdminCheckDate, ld.Address, ld.TotalArea, ld.KitchenArea, ld.ResidingFloor,
        ld.TotalFloors, ld.Price, lo.ObjectDescription, c.CityName, lt.TypeDescription,
        ca.CityAreaName, co.CountyName, lai.Information, lai.fkLanguageId
    FROM
        listings AS l
    LEFT JOIN
        listing_details AS ld
    ON
        ld.fkListingId = l.Id
    LEFT JOIN
        listing_objects AS lo
    ON
        lo.Id = l.fkObjectId
    LEFT JOIN
        listing_types AS lt
    ON
        lt.Id = l.fkTypeId
    LEFT JOIN
        cities AS c
    ON
        c.Id = l.fkCityId
    LEFT JOIN
        city_areas AS ca
    ON
        ca.Id = l.fkCityAreaId
    LEFT JOIN
        counties as co
    ON
        co.Id = l.fkCountyId
    LEFT JOIN
        listing_additional_information AS lai
    ON
        lai.fkListingId = l.Id
    
             WHERE (
                        l.fkCityId = '1'
                     AND 
                l.AdminCheck = 2
            ) ORDER BY 
                        l.Id DESC
somehow in there I have be be like 'only if there are rows in the 'listing_images' table that has the fkListingId of l.Id. Basically, it's so I can specify 'only return search results that have pictures'.

Posted: Thu Aug 03, 2006 1:02 pm
by feyd

Code: Select all

WHERE ... lai.fkListingId IS NOT NULL
Can work too.

Posted: Thu Aug 03, 2006 1:36 pm
by shiznatix
feyd wrote:

Code: Select all

WHERE ... lai.fkListingId IS NOT NULL
Can work too.
success. thanks