Jump to content

Archived

This topic is now archived and is closed to further replies.

realjumper

Query help please

Recommended Posts

I have 2 tables in my database. The database contains a ‘users’ table which contains staff details including a ‘username’ field and an ‘uploads’ field which is flagged Yes or No depending on whether any given staff member is permitted to upload files to the Intranet server.

I do not keep email addresses in this table because some users have more than 1 email address….I keep them in a table named ‘email’. In the email table I have 2 fields, 1 for username and 1 for email.

So what is common to these tables in the username fields.

What I want to do is to select all the email addresses of staff who are permitted to upload files. So, in layman terms the query would look like (I think):

[code]
SELECT username, email_address FROM email AND username, uploads FROM users WHERE uploads = ‘Yes’
[/code]

So that at the end of the query I should have the email addresses of the users who are allowed to upload to the Intranet server.

I must have been staring at this for too long as I can’t get it working. Help please?!?!

Share this post


Link to post
Share on other sites
You should rethink your database design. You will be putting unecessary strain on the MySQL server and using resources inefficiently. That's not to say the strain will be noticeable (That will depend on the server and how big your user base is...among other variables), but definitely not good practice. So my suggestion would be to redesign your table structure/db design. The way you currently have it would also make you have to keep taking odd approaches to otherwise simple tasks.

Share this post


Link to post
Share on other sites
SELECT e.username, e.email
FROM email e
INNER JOIN users u ON e.username = u.username
WHERE u.uploads = 'Yes'

Share this post


Link to post
Share on other sites
Caesar, redesigning the database isn't always an option as much as we'd sometimes like it to be.  Also, if the OP was the original designer of the DB, then that's the best design they were able to come up with.  Telling them to redesign it without giving any tips or pointers is pretty pointless IMO.

Share this post


Link to post
Share on other sites
@Caesar,

Exactly what do you object to here.

Users can have more than 1 email address so that are in a separate table.

Are you advocating a single flat-file design or something, where the user table holds a list of emaill addys in a load of separate columns, or in a single column as a delimited list?

Share this post


Link to post
Share on other sites
Thank you Barand......my head is still spinning but I understand how the query works....many thanks. FWIW....the database has to be this way as some users have 5 or 6 email addresses so I would think that having a separate email table makes sense....especially as other users details like 'address', 'department', 'phone number' etc etc are kept in the users DB with an auto increment 'id' field. Having email addresses in a separate table makes sense to me...although I may be wrong of course!

Share this post


Link to post
Share on other sites
I don't have a problem with the db design. It seems a typical 1-to-many relation to me.

Perhaps Caesar may want to enlighten us.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.