Need MySQL Query Help

[Falcon Capt]

I am trying to find users who have had one subscription but not the other.

I am using this query to locate one of the subscription types:

SELECT username
FROM subscriptionlog
LEFT JOIN user ON (user.userid = subscriptionlog.userid)
WHERE subscriptionid = 6 AND status = 0 AND usergroupid = 2

Now out of this group I need to find ONLY the users who have NOT had subscriptionid = 1.

Basically, if there userid exists in subscriptionlog more than once (indicating more than one subscription has existed for the user) I don't want the username returned. ONLY users who have had subscriptionid 6 and NOT 1.

Any help would be greatly appreciated!

[ranz]

...
WHERE subscriptionid = 6 AND subscriptionid <> 1 AND status = 0 AND usergroupid =2

should do it.

[Falcon Capt]

Originally Posted by ranz

...
WHERE subscriptionid = 6 AND subscriptionid <> 1 AND status = 0 AND usergroupid =2

should do it.

I had already tried that exact solution but the problem is the 2 subscriptions are on different rows. So using the above there will never be a subscriptionid of 6 & 1 on the same row, so the above returns EVERYONE who has had a subscriptionid = 6 regardless of whether they have had a subscriptionid = 1 or not. I need to have it not return people who have had both 1 & 6 and only return those who have only had subscriptionid = 6.

I need a query that checks for multiple entires (row) having the same userid and different subscriptionid's and not return a result for userid's that have more than one entry (row).

[ranz]

OPTION1: Do some code to achieve this. You will need to use the result of one query (and it's rowset) to produce the internal result of the other.

you can throw the recordid into the internal query from the external one - which will give you your results your after.

some pseudocode (do not use example only):

PHP Code:

$resultouter = $db->query_read("
    SELECT id, username
    FROM subscriptionlog
    LEFT JOIN user ON (user.userid = subscriptionlog.userid)
    WHERE subscriptionid = 6 AND status = 0 AND usergroupid = 2
");

while( $rowouter = $db->fetch_array($resultouter){

    $resultinner = $db->query_read("
        SELECT username
        FROM subscriptionlog
        LEFT JOIN user ON (user.userid = subscriptionlog.userid)
        WHERE subscriptionid = 1 AND status = 0 AND usergroupid = 2
        AND subscriptionlog.id = " . $rowouter['id'] . "
    ");
    
    while( $rowinner = $db->fetch_array($resultinner){
    
        // do something here with the query result? populate an array?
    
    }
    $db->free_result($resultinner);

}
$db->free_result($resultouter); 


OPTION2: You could also look at INNER_JOIN and OUTER_JOIN complex queries, it may provide some assistance. Visit mysql site and lookup INNER_JOIN.

OPTION 3: You could look at SELECT ... WHERE field IN(SELECT ... WHERE) queries.

hope this helps.