related table:
Table User (user_id,username)
Table UserDpt (user_id,dpt_id)
Table Dpt (dpt_id,dptname)
With the appropriate PK, FK and relational integrity set up.
Then
SELECT u.username, d.dptname
FROM User u, UserDpt ud, Dpt d, UserDpt ud2
WHERE u.user_id = ud.user_id
AND ud.dpt_id = d.dpt_id
AND u.user_id = ud2.user_id
AND ud2.dpt_id = <cfqueryparam cfsqltype="cf_sql_..."
value="#searchvariable#">
ORDER BY u.username
Then do a grouped output of the users and departments
If you really can't change the db, post the question again and I will
provide the ugly and slow solution using the bad DB-design
Pascal
> -----Original Message-----
> From: vishnu prasad [mailto:[EMAIL PROTECTED]
> Sent: 08 October 2004 12:46
> To: CF-Talk
> Subject: Urgent:coldfusion report
>
> Hi All
>
> i have a prblme in a displaying a report
> Table username
> username dep_code
> ----------------------------------
> testuser Sal,Pur
> testuser1 sal,Acc
> testuser3 acc,pur
>
> TAble department
> dpet_id Name
> ---------------
> Sal Sales Department
> Pur Purchace Department
> ACC Accounts Department
>
> in the report search criteria i will give the department input as Sal
, in
> the output i want
> to display the userlist who belong to the department sal specfied in
the
> saerch and also it has to display if he belong to other department
also
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
