Linking 2 Tables and Selecting Unique Data

jona2004 · #1 (**permalink**) 03-30-06, 09:47

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

r937 · #2 (**permalink**) 03-30-06, 10:08

Code:

select users.username
  from users 
left outer 
  join employees
    on employees.username = users.username
 where employees.username is null

jona2004 · #3 (**permalink**) 03-30-06, 10:19

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

jona2004 · #4 (**permalink**) 03-30-06, 12:15

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

r937 · #5 (**permalink**) 03-31-06, 07:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech