Any join wizard please help.
A have a table "users" of users, for this matter let's say with two fields: id (int) and name (varchar).
I also have a table "replies" with various user replies.
Fields: user_id (int), input_id (int), reply (varchar)
This table works as one entry per reply – if a user replies to a form with four input-fields this is recorded as four lines in this table. The inputs are identified by input_id.
OK. The task is, being presented a number of input field ids (lets still say four), to retrieve a result set of users having replied to these fields and their replies.
This would be a simple join, BUT i want a row even if the given user has replied to less than all of the given inputs.
IE there may not be matches for all inputs. No matter which combination of LEFT and RIGHT JOINs i try I seem to leave out some.
Let's say i want a result set for inputs 3, 7, 11 and 13:
I try:
SELECT u.name, r1.reply, r2.reply, r3.reply, r4.reply
FROM users.u
RIGHT JOIN replies as r1 ON r1.user_id = u.id
RIGHT JOIN replies as r2 ON r1.user_id = u.id
RIGHT JOIN replies as r3 ON r1.user_id = u.id
RIGHT JOIN replies as r4 ON r1.user_id = u.id
WHERE r1.input_id = 3
AND r2.input_id = 7
AND r2.input_id = 11
AND r2.input_id = 13
This goes fine including the first right join, i'll even get a row for users not present in users table.
But on the second right join it fails. I surely will get all users in r2 (input 7) regardles of their presence in r1 but any user having replied to input 3 but not input 7 is now left out.
Maybe I'm thinking this all wrong, but it's been bugging me for hours, so I'm here...
A have a table "users" of users, for this matter let's say with two fields: id (int) and name (varchar).
I also have a table "replies" with various user replies.
Fields: user_id (int), input_id (int), reply (varchar)
This table works as one entry per reply – if a user replies to a form with four input-fields this is recorded as four lines in this table. The inputs are identified by input_id.
OK. The task is, being presented a number of input field ids (lets still say four), to retrieve a result set of users having replied to these fields and their replies.
This would be a simple join, BUT i want a row even if the given user has replied to less than all of the given inputs.
IE there may not be matches for all inputs. No matter which combination of LEFT and RIGHT JOINs i try I seem to leave out some.
Let's say i want a result set for inputs 3, 7, 11 and 13:
I try:
SELECT u.name, r1.reply, r2.reply, r3.reply, r4.reply
FROM users.u
RIGHT JOIN replies as r1 ON r1.user_id = u.id
RIGHT JOIN replies as r2 ON r1.user_id = u.id
RIGHT JOIN replies as r3 ON r1.user_id = u.id
RIGHT JOIN replies as r4 ON r1.user_id = u.id
WHERE r1.input_id = 3
AND r2.input_id = 7
AND r2.input_id = 11
AND r2.input_id = 13
This goes fine including the first right join, i'll even get a row for users not present in users table.
But on the second right join it fails. I surely will get all users in r2 (input 7) regardles of their presence in r1 but any user having replied to input 3 but not input 7 is now left out.
Maybe I'm thinking this all wrong, but it's been bugging me for hours, so I'm here...