SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Want to show rows from table1 that are not in table2

    The tables:

    USERTABLE

    username ID
    ----------------
    john 1
    peter 2
    mary 3

    SUBJECTTABLE

    subject ID
    ----------------
    some text 1
    other text 2

    LOOKUP

    UID SID
    -----------------
    1 1
    1 2


    Let's say that only the username 'john' was know before a select.
    If I wanted to show all of john's associated subjects registered in the LOOKUP table I would do:

    SELECT subject, subjecttable.id from usertable, subjecttable,lookup where username = 'john' and UID=usertable.id and SID=usertable.ID;

    But how dow I do this then given the username 'john'.

    I now want to show text from the subjecttable where the user 'john' has not records in the LOOKUP table.

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hjp
    If I wanted to show all of john's associated subjects registered in the LOOKUP table I would do:

    SELECT subject, subjecttable.id from usertable, subjecttable,lookup where username = 'john' and UID=usertable.id and SID=usertable.ID;
    Is the part in blue correct? Looking @ the table, it looks like John has 2 diff subjects. Using your 2nd condition though, you would only get 1 subject back...why are you checking to make sure the Lookup.SID = userTable.ID?

    The Lookup.SID column does represent a subject ID, no?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select subject
      from usertable
    inner
      join lookup
        on usertable.ID 
         = lookup.UID
    inner
      join subjecttable
        on lookup.SID
         = subjecttable.ID
     where username = 'john'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, you're killing me...
    I now want to show text from the subjecttable where the user 'john' has not records in the LOOKUP table.
    ...sounds like he wants records where 'john' (or any user) doesn't have records in the LOOKUP table? Of course if that were right though, then no records would return if he's looking for subject.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, hjp actually posted two questions, i answered only the first one

    the second one is a lot, lot harder
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    r937, you're killing me...

    ...sounds like he wants records where 'john' (or any user) doesn't have records in the LOOKUP table? Of course if that were right though, then no records would return if he's looking for subject.
    Sorry, code was wrong, this is right and gets the wanted result, namely displaying
    John's records from LOOKUP table.

    SELECT subject, subjecttable.id from usertable, subjecttable,lookup where username = 'john' and UID=usertable.id and SID=subjecttable.ID;


    But you are right.
    I want the records where 'john' (or any user) doesn't have records in the LOOKUP table. I can't figure that out.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you pays your money and you takes your choices

    here are three ways to do it

    Code:
    select username
      from usertable
     where not exists
           ( select 1 from lookup
              where UID = usertable.ID )
    Code:
    select username
      from usertable
     where ID not in
           ( select distinct UID from lookup )
    Code:
    select username
      from usertable
    left outer
      join lookup
        on ID = UID
     where UID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    you pays your money and you takes your choices

    here are three ways to do it

    Code:
    select username
      from usertable
     where not exists
           ( select 1 from lookup
              where UID = usertable.ID )
    Code:
    select username
      from usertable
     where ID not in
           ( select distinct UID from lookup )
    Code:
    select username
      from usertable
    left outer
      join lookup
        on ID = UID
     where UID is null
    I am trying hard but can't get the above code to work.
    Could you perhaps give me a code example that would give this when
    subjecttable has:
    subject ID
    text1 1
    text2 2
    text3 3
    and
    LOOKUP has:
    UID SID
    1 1
    and
    usertable has:
    username ID
    john 1
    peter 2

    Only input for the query is:
    username = 'john'

    For the outcome I would like to see:

    text2 2
    text3 3

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh, you want the subjects that john doesn't have!

    you said "I want the records where 'john' (or any user) doesn't have records in the LOOKUP table."

    i thought you mean users who have no records in the LOOKUP table

    see the difference?


    Code:
    create table subjecttable
    ( subject varchar(9)
    , ID tinyint )
    ;
    insert into subjecttable values
    ( 'text1', 1 )
    ,( 'text2', 2 )
    ,( 'text3', 3 )
    ;
    
    create table LOOKUP 
    ( UID tinyint
    , SID tinyint )
    ;
    insert into LOOKUP values (1, 1)
    ;
    
    create table usertable 
    ( username varchar(9)
    , ID tinyint )
    ;
    insert into usertable values
    ( 'john', 1 )
    ,( 'peter', 2 )
    ;
    
    
    select subject
         , subjecttable.ID
      from subjecttable
    left outer
      join LOOKUP
        on subjecttable.ID = SID 
    left outer
      join usertable
        on UID = usertable.ID   
       and username = 'john'
     where UID is null
      
    subject,ID
    text2,2
    text3,3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fantastic, I would never have figured that out.

    Don't ever give me a hot-line.

    Thanks

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, sorry about the mixup

    hot-line?

    sitepoint is your hot-line
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to bother again. But the result I get from the query is not quite what I expected.

    It is true, that if I execute your code with username = 'john' I get the result

    text2,2
    text3,3

    Problem is, that I also get that result when I execute with

    username = 'peter'

    Here I would expect the result
    text1,1
    text2,2
    text3,3

    as 'peter' has no records in LOOKUP table.

  13. #13
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this might do the job...this will return the User id, their name, and the subjects they aren't assigned to...
    Code:
    Declare @userID  tinyInt
    set     @userID = [whatever User ID you want the query for]
    
    Select  UserTable.ID,
            UserTable.Name,
            A.ID as SubjectID,
            A.Subject
    from    UserTable
    inner   join
            (Select  disinct ID,
                     Subject,
                     @userID,
                     L.SID
             from    SubjectTable ST
             left    outer join Lookup L on ST.ID = L.SID
             where   L.UID = @userID
             and     L.SID is null) as A
    on       UserTable.ID = A.@userID
    Last edited by null; Apr 23, 2004 at 12:24.

  14. #14
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    I think this might do the job...
    Code:
    Declare  @userID  tinyInt
    set      @userID = [whatever User ID you want the query for]
    
    Select ID, Subject
    from   SubjectTable ST
    left   outer join Lookup L on ST.ID = L.SID
    where  L.UID = @userID
    and    L.SID is null
    I will try that out.

    But regarding the other code
    Code:
    snippet ...
    select subject
         , subjecttable.ID
      from subjecttable
    left outer
      join LOOKUP
        on subjecttable.ID = SID 
    left outer
      join usertable
        on UID = usertable.ID   
       and username = 'john'
     where UID is null
    It seems to work when in stead of
    where UID is null
    you use
    where usertable.id is null

  15. #15
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm...well I'll let r937 explain that since it's his own query.

    I edited mine btw. Let us know if it helps.

  16. #16
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No I was wrong, the change from "UID" to "usertable.id"
    did not help either.

    Regarding NULL's suggestion:
    The query is to be made with php. I don't know how to
    Declare @userID tinyInt
    set @userID
    with PHP.

    And the other issue is as mentioned:

    username (peter, john or whatever) should be the entry point to the query. How would you implement that in your code?

  17. #17
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, if you can, run my query to make sure it's what you want first. Then go from there.

    I'm not sure about the PHP code. What DBMS? I think you can still leave the Declare and Set in there, and just reference it to a $value, or you may be able to take it out and just reference $value in the query itself, honestly I'm not sure, someone else will have to chime in, sorry man.

    ????
    Code:
    Declare @userID tinyInt
    set     @userID = $value

  18. #18
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand this code snippet:

    Select UserTable.ID,
    UserTable.Name,
    A.ID as SubjectID,
    A.Subject

    where is "A" and "SubjectID" declared?

    Also mysql complains about the declaration section.

  19. #19
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, then take out the Declaration, sorry 'bout that...but my query needs a userID where you see "@userID" in the query...not sure how you can do that w/PHP. I'm a SQL Server user, not sure how compatible this code is w/MySQL, but I think the logic should be able to carry over and give you your results.
    Code:
    Select  UserTable.ID,
            UserTable.Name,
            A.ID as SubjectID,
            A.Subject
    from    UserTable
    inner   join
            (Select  disinct ID,
                     Subject,
                     @userID,
                     L.SID
             from    SubjectTable ST
             left    outer join Lookup L on ST.ID = L.SID
             where   L.UID = @userID
             and     L.SID is null) as A
    on       UserTable.ID = A.@userID
    "A" isn't really declared, it's an alias for the subquery. Likewise w/"SubjectID", it's just an alias for A.ID (which is just the subjectID from the inner query)...does that help? Maybe someone else might have a simpler solution. I'm still not sure if my query has your solution, if you can carry over the logic to MySQL syntax, run it and see what you get. Let me know if there is something I need to explain better.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hjp
    But regarding the other code
    It seems to work when instead of where UID is null
    you use where usertable.id is null
    yes, you are right, it does
    Code:
    select subject
         , subjecttable.ID
      from subjecttable
    left outer 
      join LOOKUP
        on subjecttable.ID = SID 
    left outer
      join usertable
        on UID = usertable.ID   
       and username = 'peter'
     where usertable.ID is null
    
    subject,ID
    text1,1
    text2,2
    text3,3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, you are right, it does
    Code:
    select subject
         , subjecttable.ID
      from subjecttable
    left outer 
      join LOOKUP
        on subjecttable.ID = SID 
    left outer
      join usertable
        on UID = usertable.ID   
       and username = 'peter'
     where usertable.ID is null
    
    subject,ID
    text1,1
    text2,2
    text3,3
    Yes but only when LOOKUP table has the records:
    1 1

    now try to add to LOOKUP table

    1 2
    1 3
    2 1

    when username = 'john' I would expect no records as outcome
    but in fact I get
    text1 1
    and
    when username = 'peter' I would expect as outcome
    text2 2
    text3 3
    but get
    text1 1
    text2 2
    text3 3

    well maybe it can't be done

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sure it can
    Code:
    select subject
         , subjecttable.ID
      from subjecttable
    left outer 
      join LOOKUP
        on subjecttable.ID = SID 
    left outer
      join usertable
        on UID = usertable.ID  
       and username = 'peter'
    group
        by subject
         , subjecttable.ID
    having coalesce(max(usertable.ID),-1) = -1
    a bit of a kludge, but it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fantastic.

    I don't have the mysql version yet, but would it be possible to do the code above using subselects - and - would that be easier (for me to understand)?

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that was the mysql version

    you can tell by the multiple value in post #9

    e.g. insert into usertable values ( 'john', 1 ), ( 'peter', 2 )

    the subselect version should be a lot easier to understand, but i'm not going to test it for you
    Code:
    select subject
         , subjecttable.ID
      from subjecttable
     where not exists
           ( select * 
               from LOOKUP
             inner
               join usertable
                 on UID = usertable.ID  
              where SID = subjecttable.ID
                and username = 'peter'
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Member hjp's Avatar
    Join Date
    Aug 2003
    Location
    Denmark
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By 'mysql version' I meant, that I don't have the version that supports subselects.

    Many thanks for the code.


Bookmarks

Posting Permissions

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