SELECT role.roleid, role.rolename, userrole.userID FROM Role AS role INNER JOIN userrole AS userrole ON role.roleid = userrole.roleid WHERE role.applicationid = 39 AND userrole.userID = 84
try that. should only return results that have the same roleid and match the other two criteria. hope thats what you want, otherwise just play with the WHERE statement -----Original Message----- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 29 January 2003 16:51 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL: Completely half asleep Okay... Full code: SELECT roleid, rolename FROM Role WHERE applicationid = 39 Result: Roleid Rolename ------------------------ 145 Application Editor 146 User Editor 147 Guest 148 Editor 149 Another Role ------------------------ SELECT roleid, userid FROM userrole WHERE userid = 84 Roleid userid ------------------------ 145 84 148 84 147 84 ------------------------ Okay... The roleid is the PK in the role table and is the FK in the UserRole table What I want is: Roleid Rolename UserID(or whatever) ------------------------------------- 145 Application Editor 84 146 User Editor NULL 147 Guest 84 148 Editor 84 149 Another Role NULL ------------------------------------- Make sense now? I always find it annoying when people make assumptions on code based on the titles of fields, so I generally try and get generic answers instead of what people "think" they know about the database. Paul > ok hang on, > > how can you get this as the final result: > > Fld1 Fld2 Fld4 > 145 val1 vala > 146 val2 valb > 147 val3 NULL > 148 val4 valc > 149 val5 NULL > > (ir fld4 to have multiple values) when you want fld4 = 84? > > or perhaps I'm not understanding. > > > > > -----Original Message----- > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > Sent: 29 January 2003 15:52 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > > SELECT a.fld1, a.fld2, b.fl4 > > > FROM tbl1 a > > > LEFT INNER JOIN tlb2 b > > > ON a.fld1 = b.fld1 > > > ORDER BY a.fld1 > > > > > > > Doesn't work.. Problem is that I have criteria to match on > both sides. > > > > Remember I need to test what a.fld3 is and b.fld4 is > > > > It's just a bit strange, because ths query is easy to do: > > > > SELECT a.fld1, a.fld2, b.fld4 > > FROM tbl1 a, tbl2 b > > WHERE a.fld1 = b.fld1 > > AND a.fld1 = 84 > > AND b.fld4 = 39 > > > > But this gives: > > > > Fld1 fld2 > > 145 val1 > > 146 val2 > > 148 val4 > > > > And I need the other two rows in there with a null value... > > > > Paul > > > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > [EMAIL PROTECTED] For > > human help, e-mail: [EMAIL PROTECTED] > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
