Jump to content
  • Advertisement
Sign in to follow this  
benryves

[web] SQL: Selecting a list of names and application IDs from two tables

This topic is 4895 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

We have two tables to compare, users and equalopps. Each user has an appid associated with it (once migrated from the "applicants" table, they take the appid with them). When someone has filled in an equal opportunities questionnaire, the appid is recorded on the equalopps table. The query: select users.appid, users.first_name, users.last_name from equalopps, users where users.appid = equalopps.appid ...produces a result of all the people where they have filled in the equal opportunities questionnaire. However, I want a list of people who haven't. Switching the conditional around woudn't work (you end up with the same names repeated again and again) - is there an easy (SQL-based) way around this? Or will I have to muck around with subqueries? I could do this fairly easily in PHP, but I'd rather do it in the SQL query. EDIT: Well, select appid, first_name, last_name from users where not exists (select appid from equalopps where users.appid = equalopps.appid); works, but I seem to remember being told that nesting queries like this was a Bad Thing... [Edited by - benryves on July 20, 2005 5:58:14 AM]

Share this post


Link to post
Share on other sites
Advertisement
Been a while, but this sounds like you need a JOIN to me.


SELECT users.appid, users.first_name, users.last_name
FROM users
LEFT JOIN equalopps
ON users.appid = equalopps.appid


I have a sneaking feeling that will return all the rows anyway, but it is something along those lines I believe.

Share this post


Link to post
Share on other sites
Quote:
Original post by John H
Been a while, but this sounds like you need a JOIN to me.


SELECT users.appid, users.first_name, users.last_name
FROM users
LEFT JOIN equalopps
ON users.appid = equalopps.appid


I have a sneaking feeling that will return all the rows anyway, but it is something along those lines I believe.


This will return all users including users that have not filled out the questionaire. All thats left is to filter the results to remove people that have filled out the questionaire.

SELECT users.appid, users.first_name, users.last_name
FROM users
LEFT JOIN equalopps
ON users.appid = equalopps.appid
WHERE equalopps.appid IS NULL

This will only select users that have no matching equalopps entry.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!