Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    16

    Question Unanswered: Linking 2 Tables and Selecting Unique Data

    Hi,

    I have two tables, a list of users for a system, and a list of employee's who user the system.

    The tables are as follows:

    Users
    ----------
    user1
    user2
    user3

    Employee
    ----------
    user1
    user2

    User 3 can login into the system but has limited functions, I need to be able to select all user who are not currently employee's.

    I know this can be done and I have tried a number of ways to get around it.

    Basically I need a query select all users from the user table who do not appear in the emplyee table.

    Anyone know how to get around it, they are both innodb mysql tables by the way, linked using the field username.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select users.username
      from users 
    left outer 
      join employees
        on employees.username = users.username
     where employees.username is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    16
    Quote Originally Posted by r937
    Code:
    select users.username
      from users 
    left outer 
      join employees
        on employees.username = users.username
     where employees.username is null
    Spot on, thanks for the help, exactly what I wanted to do.

    Cheers

  4. #4
    Join Date
    Jan 2005
    Posts
    16

    Question

    Thanks for the great help so far,

    Now this is where it gets a little harder.

    I have three tables which I must combine to view information.

    These are the tables I have:

    user
    -----------
    username
    section_id

    booking
    -----------
    booking_id
    username
    authorised tinyint(1) can be 1 (authorised) and 0 (unauthorised)

    section
    -----------
    section_id

    What I am trying to do is, list all bookings which are not authorised (ie 0) for a specific section.

    However the links are like this

    user | booking |
    ------------------------
    | booking_id |
    username->| username |
    section_id-|------------|

    I figured out that I only need the 2 tables, user and booking, as the section id is held in the user table so I can use that to check

    However for each booking I must get the username from the booking table go into the user table, get the section id and compare it to the section id eneterd by the user and I am having no luck in doing this, anyone know how to do it.

    I just need the sql query that joins the tables and I can't get my head around it.

    Any help would be appreciated.

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jona2004
    However for each booking I must get the username from the booking table go into the user table, get the section id and compare it to the section id eneterd by the user
    Code:
    select booking_id
      from booking
    left outer
      join user
        on user.username = booking.username
       and user.section_id = 937
     where booking.authorized = 0
       and user.username is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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