SQL "In" Statement Returning 6 Records...overall SQL statement returns 22 Records

I am generating the following SQL statement dynamically with PHP and am having an issue with the records it's returning. The "IN" statement located in the middle with the large list of comma separated values returns 6 records for a particular islandID. However, the final results that are generated when running the entire statement gives me 22 records for the same islandID mentioned above...any help is appreciated. I can provide more info if needed.

SELECT islandID AS parentIslandID, islandName, island.longDesc,
imageLocation, COUNT(resortID) AS totalResorts, resort.resortID
FROM island, images, resort
WHERE parentIslandID = island.islandID
&& parentObjectID = island.islandID
&& imageType = 1
&& island.islandID IN (
    SELECT parentIslandID FROM resort WHERE resortID IN (
        59,62,65,69,71,72,74,75,76,82,86,89,91,93,95,105,
        106,116,117,118,120,121,122,123,124,125,126,127,
        131,145,146,150,157,159,160,167,170,174,176,185,188,189,193,
        194,198,199,200,203,205,213,217
    )
)
&& (search_Inclusive = '-1' || search_Inclusive = '0')
&& (search_onBeach = '-1' || search_onBeach = '0')
&& (search_wedCoord = '-1' || search_wedCoord = '0')
&& (search_roomRate >= '0' && search_roomRate <= '1000')
&& (search_HotelSuite = '-1' || search_VillaCondo = '-1')
&& (passportReq = '-1' || passportReq = '0')
&& (wideAccept = '-1' || wideAccept = '0')
&& (daysSearchable <= '3')
&& (search_airportDist <= '6')
&& resort.active = '-1'
GROUP BY resort.parentIslandID
ORDER BY totalResorts DESC

New thought: I am doing pretty much the same thing with an "IN" operation with another table. The only difference is said table contains the islandID which I used in my "IN" statement. So I plan on creating a view in开发者_如何学运维 the DBMS to merge two tables to get me the islandID and work with it this way. Any thoughts on this?

My guess is that you're joining on 3 tables (island, images, resort) and the resorts/images is a many-to-one relationship?

So for a particular IslandID, as you say, you have 6 resorts. But if each resort has 3 pictures, you'll get 18 rows returned total, 3 for each island/resort combination.

As noted above, it's difficult to answer this question without seeing your database. One other possible I can see is that you are not grouping by all the columns you are selecting. In oracle this would cause an error and the query would not run.

I'm not sure what happens in php, but at least according to this link

http://www.w3schools.com/sql/sql_groupby.asp

It could be something like what you are experiencing. Try adding all the other columns that are not aggregate functions, i.e.

islandName, island.longDesc, imageLocation, resort.resortID

to your group by clause.

This is really hard to answer without knowing your DBMS but;

Does the resort table allow nulls in the parentIslandID column?

If so this may confuse your subquery as it introduces a 3rd outcome (i.e. from TRUE/FALSE to TRUE/FALSE/UNKNOWN).

To resolve this, two options are:

a) change the schema for the table to not allow nulls in the parentIslandID column and provide a default value (preferred) b) modify your query with coalesce / isnull (syntax dependant on your DBMS):

[...]    
island.islandID IN (
SELECT coalesce(parentIslandID,-1) FROM resort WHERE [...]

For your further reference, again dependant on your DBMS: http://msdn.microsoft.com/en-us/library/ms191504.aspx