Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Question on Joining

    I am having difficulty understanding the different joins.

    I have two tables with these fields.

    users
    -- userid
    -- department

    applications
    -- userid
    -- application name

    The users table would have unique ids such as

    bill - Accounting
    fred - Accounting
    joe - HR

    The applications table will have duplicate id's such as

    bill - paychecks
    bill - payables
    fred - payables
    fred - billing
    joe - hiring


    I am trying to add everyone from one department to the applications table, but only if they are not already listed in the applications table. The first step is to pull the list of id that I can insert. In the above example, if I selected the Accounting department and billing application, bill needs to be found, but fred and joe should not be on the list.

    I have tried several different join statements, but I got lost trying to pull only those people that match the department and do not have that specific id/application entry in the applications table.

    I suppose that I could do this by pulling a list of everyone in Accounting and then doing a query for each of them to see if the id/application combination is already listed, but I would rather do it in one query if possible.

    I have tried looking on mysql.com, but I could not make much sense of the join section.

    Is there a join statement that will do this?

    Thanx for any help you can give.

    JustLearning

    ~~~ Pretend that I have a long and creative signature ~~~

  • #2
    New Coder
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this

    select users.userid from users where users.userid not in (select distinct userid from applications);

    If applications is a large table this will be really slow. This is Oracle syntax so you may have to alter it for your database type.


    Rita
    Rita P.

  • #3
    New Coder
    Join Date
    Jun 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Thank you for you answer. Unfortunately, MySQL does not support sub-selects unless part of an insert or delete query. I will see if I can use this to make something that will work, but it does not look promising.

    Thank you anyway.

    JustLearning
    ~~~ Pretend that I have a long and creative signature. ~~~


  •  

    Posting Permissions

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