Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unhappy Unanswered: Another Join problem

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Siggenthal, Switzerland
    Posts
    19
    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

  3. #3
    Join Date
    Feb 2004
    Posts
    11

    Talking

    WORKS !

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

    Thanx again

    Ali

  4. #4
    Join Date
    Feb 2004
    Posts
    11
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •