October 24, 2003 at 4:25 am
I need to exclude records from a query result set if the creator of the record has included marked a record as private (via a field called USAGE_CODE) when other users_ids do not match the creator_ids where user_id are used to populate the creator_id field.
If the query is done by the user_id that is the same as the creator_id, the "private" images get included in the query result set, otherwise they do not get included in the query result set.
Visual FoxPro SQL code premits an "immediate if" to be included; does SQL Server also permit immediate ifs ? Or, how otherwise is the kind of conditional query handled?
Thanks for any advice...
Arden
October 24, 2003 at 5:52 am
I would think you use a WHERE clause...
SELECT <required fields>
FROM MyTable
WHERE (creator_id=@user_id
OR USAGE_CODE = 0)
<Any additional selections>
Replace @user_id with a variable representing the user executing the query.
In this code, a record is marked private if USAGE_CODE differs from 0.
October 24, 2003 at 7:29 am
I understand the where clause usage (I think). See pseudo code to hopefully clarify the intent of the condition query.
IF mUSER_ID = IMS.CREATE_BY THEN
SELECT * FROM IMS
ELSE
SELECT * FROM IMS WHERE IMS.USAGE_CODE
<> "PRIVATE"
ENDIF
The fly in the oinment is that the CREATE_BY field is part of the table being queried not and external variable.
I know I can get all the pictures back and then exclude those marked as private and where USER_ID <> CREATE_BY -- but wanted the database to do the heavy lifting. In VFP it is as simple a using and immediate IF statement in the where clause, but I don't seem to be able to find that feature supported by SQL Server or ODBC against ACCESS.
Arden
October 24, 2003 at 7:40 am
Just use
SELECT *
FROM IMS
WHERE IMS.USAGE_CODE <> "PRIVATE"
OR IMS.CREATE_BY = m_UserId
If you pass the query as a string into a 'command' or 'recordset' object, you can replace the 'm_UserId' by the value you want it to match.
If you use a stored procedure to return the recordset, you should use an input parameter.
October 24, 2003 at 9:19 am
Thanks for your help -- I should have been able to figure that out all by my lonesome -- too late at night I guess (lame excuse).
Arden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply