querry involving multiple tables.

[rammie singh]

hi evry body

i have following tables

USER

EMP_ID(Int)(PK)
FIRST_NAME( VarChar2(20) )
LAST_NAME( VarChar2(20) )
USER_STATUS(int)
PASSWORD( Varchar2(10) )


ROLE

ROLE_ID(Int)(PK)
EMP_ID(Int)(FK--> User.EMP_ID)
ROLES( varchar2(15) )


PRIVILEGE

PRIV_ID(Int)(PK)
EMP_ID(Int)(FK--> User.EMP_ID)
PRIVILEGES( varchar2(15) )




now i want to display name ,id , along with roles and privileges

of all emplyees who have privileges ="SAP" and ROLES="SME"

what could be the possible ways.

i am using MYSQL

thanks and regards.

[rammie singh]

i tried these queries


select USER.`EMP_ID`,USER.`FIRST_NAME`,USER.`LAST_NAME`,PRIVILEGE.`PRIVILEGES`
PRIVILEGE.`START_DATE`,PRIVILEGE.`END_DATE`,ROLE.`ROLES`,ROLE.`START_DATE`,ROLE.`END_DATE`,
from USER
left join PRIVILEGE on USER.EMP_ID=PRIVILEGE.`EMP_ID` and PRIVILEGE.`PRIVILEGES`="SAP"
left join ROLE on USER.EMP_ID=ROLE.EMP_ID and ROLE.ROLES="SME";


and



select USER.`EMP_ID`,USER.`FIRST_NAME`,USER.`LAST_NAME`,PRIVILEGE.`PRIVILEGES`
PRIVILEGE.`START_DATE`,PRIVILEGE.`END_DATE`,ROLE.`ROLES`,ROLE.`START_DATE`,ROLE.`END_DATE`,
from USER,PRIVILEGE,ROLE

where (USER.EMP_ID=PRIVILEGE.`EMP_ID` and PRIVILEGE.`PRIVILEGES`="SAP") and (USER.EMP_ID=ROLE.EMP_ID and ROLE.ROLES="SME");


but not getting executed