select command..

dansoft

I want to add the names of the staffs, in the dropdownlist, who's login is not yet created. Eg. consider 2 tables staff, and login staffs: A,B,C,D,E login: B,E the logins are created for B and E so in the dropdownlist it must display A,C,D i tried the following query but it is not working. select sname from staff,login where staff.sname!=login.[name] It leads to duplicated records..... Please help!!

Mike Dimmick

You're using some very old join syntax there. The following should work for you:

SELECT sname
FROM staff
LEFT JOIN login
ON staff.sname = login.[name]
WHERE [name] IS NULL

How it works: the LEFT JOIN outputs all rows from the left-hand side joined to the right-hand side where the ON condition evaluates to true. If there is no match, it outputs the left-hand side row with NULLs in all the fields from the right-hand side. Because we want the rows which don't have a match, we select only the rows which have NULLs in the right-hand side. If we wanted only the rows that did match, I'd use an INNER JOIN. That gives only rows where the ON condition evaluates to true. Stability. What an interesting concept. -- Chris Maunder

albCode

select staff.* , login.* from staff,login where staff.sname <> login.name Proud to be Albanian

turbochimp

This will also work, if I understand your question correctly (i.e. you want to know what staff members do not have a corresponding record in the login table):

SELECT
    s.sname
FROM
    staff s
WHERE
    s.sname NOT IN (SELECT name FROM login)

or this...

SELECT
    s.sname
FROM
    staff s
WHERE
    NOT EXISTS (SELECT 1 FROM login l WHERE l.name = s.sname)

The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’