Select problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Select problems

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

INNER JOINs will only return rows when there is data found to match the constraint.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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'.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

WHERE ... lai.fkListingId IS NOT NULL
Can work too.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

feyd wrote:

Code: Select all

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