SELECT a.roleid, a.rolename, b.userid FROM role a LEFT JOIN userrole b ON a.roleid = b.roleid WHERE a.applicationid = 39 OR b.userid = 84
? Its much easier when we can see exactly what the tables look like. > -----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]
