|
I will come back and post my answer here.
I want to create a table but with only one user's record.
I used Join in my Select statement to connect the #__users table and the #__facileforms_subrecords table.
But to get all the #__facileforms_subrecords records that match one user, I need to know the #__facileforms_subrecords.recordk and the #__users table does not use that value in any of it's fields (so I can't join with that field).
So I need to first find the value of #__facileforms_subrecords.record for the current user, then use that value to find the rest of the records.
I can find the #__facileforms_subrecords.record for the current user by doing this select statement.
SELECT
#__facileforms_subrecords.value,
#__facileforms_subrecords.title,
#__facileforms_subrecords.record,
#__users.name,
#__users.id
FROM #__users
RIGHT JOIN #__facileforms_subrecords
ON #__users.name=#__facileforms_subrecords.value
WHERE #__users.id = {$UserId}
I want a report that includes:
WHERE #__facileforms_subrecords.name IN ("sparkname", "name", "Week1ChalPts", .....)
Order By Case #__facileforms_subrecords.name
When "sparkname" Then 1
When "name" Then 2
When "Week1ChalPts" Then 3
,,,,,,
Else n End
But if I do both WHERE parts as one WHERE with an AND, I only get the element that has the users.name value in it.
If I do an OR, I get all the records including ones that don't belong to the user running the report.
How do I get a record ID for the current user, using the first SELECT and then find all the records that belong to that user using the WHERE declared just above?
Can I store a variable from the first Select then use that variable? I tried this and got errors, so please give exact syntax if this is possible.
Is there another way to create a report for just one user?
Thanks, Jane
|
