realjumper Posted September 25, 2006 Share Posted September 25, 2006 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?!?! Quote Link to comment Share on other sites More sharing options...
Caesar Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 25, 2006 Share Posted September 25, 2006 SELECT e.username, e.email FROM email eINNER JOIN users u ON e.username = u.usernameWHERE u.uploads = 'Yes' Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 25, 2006 Share Posted September 25, 2006 @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? Quote Link to comment Share on other sites More sharing options...
realjumper Posted September 25, 2006 Author Share Posted September 25, 2006 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! Quote Link to comment Share on other sites More sharing options...
Barand Posted September 25, 2006 Share Posted September 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.