Hi there,

<you can skip introduction and go to the situation>

I have designed little database(4 tables) for a voice mail system which recognises a user based on his phone#, Now I have a table called Profiles which contains unique user profiles , users must be able to listen to others profiles , but once a user hears a profile , he musn't hear it again , so I created another table called profile_history which associates user profiles with the profiles they heared , I must be able to select all profiles that have not been heared by a specific user .

The situation is as follows

Profiles
+-----------------+
| profile_id | bla|
+----------------+
| 1 | bla |
| 2 | bla|
| 3 | bla|
| 4 | bla |


Profile_history
+-------+
| profile_id | profile_legend |
+-------+
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 3 | 2 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |


Now how can I select all records in Profiles

Where Profiles.profile_id <> profile_legend
And profile_history.profile_id=1

(means that I must select profile_id 1 and 4 from Profiles in this specific case)

Its not as simple as it looks like

Or is it something else that I can change in the tables structure ?

ur urgent help is truly appreciated

Hi you,

Try this:
SELECT p.profile_id, p.bla
FROM profiles p, profile_history h
WHERE h.profile_id=1
AND p.profile_id<>h.profile_legend
GROUP BY p.profile_id
HAVING COUNT(p.profile_id)>=2

Idea: the user with count=1 are not yet in the history table.

Walter

WORKS !

Thanx a lot Walter
you really saved me a lot of time

Thanx again

Ali

In fact , and after testing , in order for this query to work properly ,
it should be like this

SELECT p.profile_id, p.bla
FROM profiles p, profile_history h
WHERE h.profile_id=1
AND p.profile_id<>h.profile_legend
GROUP BY p.profile_id
HAVING COUNT(p.profile_id)>=(select count (h.profile_id) from profile_history h whereh.profile_id = 1)

(of course sub queries are not there, turn around)