MS Access query help

davestar_delux

davestar_delux

davestar_delux

Soldato
Joined
5 Dec 2003
Posts
2,716
Location
Glasgow
Hey I can't seem to get my head around this query I'm trying to do.

I have 3 tables like so:

[courses]
courseIDPK
coursename
signUps

[users]
usernamePK
password
signUps

[usercourses]
userCourseIDPK
userName
CourseID

I want a query that will check if users.signUps is less than 2, courses.signUps is less than 15 and there isn't a record in usercourses with the username and courseID provided already.

Then I want to add that record and add 1 to both users.signUps and courses.signUps.

Is this possible?
 
davestar_delux said:
Hey I can't seem to get my head around this query I'm trying to do.

I have 3 tables like so:

[courses]
courseIDPK
coursename
signUps

[users]
usernamePK
password
signUps

[usercourses]
userCourseIDPK
userName
CourseID

I want a query that will check if users.signUps is less than 2, courses.signUps is less than 15 and there isn't a record in usercourses with the username and courseID provided already.

Then I want to add that record and add 1 to both users.signUps and courses.signUps.

Is this possible?
Click to expand...

You'd be better off having a signups table, with userid and course id, so you could do something as simple as 'SELECT COUNT(*) from signups where course_id = blahblahblah', or 'SELECT COUNT(*) from signups where user_id = blahblahblah'.

Make sure that the (user,course) combo is unique amnd you can also ensure that users cant sign on to one course more than once.
 
Hi,

Try something like this:

Code: 
select 1
from users u, courses c, usercourses s
where u.signups < 2
and c.signups < 15
and s.username = u.usernamepk
and s.usercourseIDPK = c.courseIDPK
and 1 not in 
(select 1 from usercourses s2 where 
s2.username = $providedusername
and s2.courseid = $providedcourseid);

Not too sure if that's completely correct, or if the syntax is acceptable by the DB you're using, but it should get you on the right path.

If the SELECT returns a value of 1 in it's result set then you can run another statement to insert the new row. The subselect picks out whether the row already exists on the usercourses table.

You might be able to combine the Insert with the Select but the complexity and unreadability might not be worth it.

You might also want to check the key on the users table. I don't know for sure but it looks like the key is on the user's name. If so, keying on a string isn't very reliable as SQL is case-sensitive etc. (Converting everything to upper/lowercase in where statements etc. would probably cause the key to be unused.) IMHO it's better to create a numeric key (which is mainly for the system's use). You might also find it hard to identify users by their name so again, a numeric key would help here.

Hope that helps.
Jim
 
KingAdora said:
I love it when people post their homework here with no sign that they've tried anything... Then when the answer is posted, no thanks is given, and they make a new thread for the next part of their homework.

Great stuff!
Click to expand...

my point exactly, the OP will find that he get's less and less help as he does it more.
 
To be honest I solved things myself before anyone replied and i've been at work all day so not had a chance to read any replies on here.

Thanks to those that posted something constructive though.
 
You must log in or register to reply here.
Back
Top Bottom