[SOLVED] Excluding results with ! operator

[Blackguard]

I have a table like such

<pre>TABLE ids

id -- key

1 | 3

1 | 4

1 | 5

2 | 3

2 | 2

3 | 5

</pre>

 

and one as such

 

<pre>TABLE names

id -- name

1 | bob

2 | jim

3 | joe

</pre>

 

I'm trying to get all the names associated with one key but not any other.  For example, I'd like to get the names associated with key 5 but not associated with any other key.

 

I'm trying to do it like this, but it doesn't work :

 

<pre>

SELECT names.name FROM names, ids WHERE names.id = ids.id AND ids.key <> 3 AND ids.key <> 4 AND ids.key = 5

</pre>

 

In this case, my hoped for result would be only joe.  But it doesn't work like this apparently.  Do I need to JOIN the table to itself?

[cooldude832]

well if we think about this if a name is only associated with a single "key" than the COUNT for that ID would = 1 so why not say

 

select names.name from `ids` LEFT JOIN `names` ON(ids.id=names.id) where COUNT(ids.id) =1 and ids.key=$keynumber GROUP BY names.name

 

I think that will work  I added the ids.key=$keynumber saying if you want to do a specific key only than specify a key number.

[Blackguard]

Yes, thanks for the suggestion, I think this would work, and I was exploring going down that route.

 

But I didn't explain my problem completely enough.

 

Sometimes I will need to get a result for more than one key and exclude results that include all other keys.

 

For example,

<pre>

TABLE ids

id -- key

1 | 3

1 | 4

1 | 5

2 | 3

2 | 5

3 | 4

3 | 5

</pre>

 

<pre>

TABLE names

id -- name

1 | bob

2 | jim

3 | joe

</pre>

 

Let's say I want to get all names that are associated with keys 4 and 5 from the ids table.

 

If I try to use GROUP BY and COUNT(ids.id) = 2, jim will creep into my result set because key 5 qualifies him and COUNT(ids.id) = 2 is true.

 

Maybe I need an inner query?  Or a join on the keys table with itself?

[cooldude832]

Edit:

Let me rethink this...

 

thought of an alternative method using an IN NOT IN method

 

You want people who are IN the keys requested by NOT IN Other keys (i'm going to use a bit of php just to aid in the example)

 

The first IN clause will get you the name of all people who are connected to the valid keys

The second one basically says get me any id that is  part of a key other than a valid key eliminating people who have the requested key(s) but also have other keys.

 

 

<?php
$keys = array(3,4);
$q = "
SELECT 
	names.name 
FROM 
	`names`
WHERE
	names.id IN(
					SELECT id from `ids` WHERE key IN(". implode(", ", $keys) .")
				)
	AND
	names.id NOT IN(
					SELECT id from `ids` WHERE key IN(Select key from `ids` Where Key NOT IN(".implode(", ", $keys)."))
				)
";
?>

 

Might have over complicated it I think oh well

[Blackguard]

Thanks a lot, this has solved my problem.  I can definitely use this!