I prefer doing the joins in the from part rather than the where part... ----- Original Message ----- From: "Rich Wild" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 31, 2003 9:45 AM Subject: RE: [ cf-dev ] SQL: Completely half asleep
> its probably a personal thing. I prefer writing the words, I find I can see > quicker what's going on. > > > -----Original Message----- > > From: Snake [mailto:[EMAIL PROTECTED]] > > Sent: 31 January 2003 09:37 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > I think It's easier to use *= on SQL server than LEFT OUTER > > JOIN and =* > > rather than RIGHT OUTER JOIN, tidier to. > > > > SELECT roles.roleid, roles.rolename, userrole.userid > > FROM roles, userrole > > WHERE roles.roleid *= userrole.roleid > > AND userrole.userid = 84 OR userrole.userid IS NULL > > > > Russ > > > > -----Original Message----- > > From: Spike [mailto:[EMAIL PROTECTED]] > > Sent: 29 January 2003 17:59 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > And another one with the userid column instead of a second roleid > > column. > > > > sheesh! > > > > SELECT roles.roleid, roles.rolename, userrole.userid > > FROM roles LEFT OUTER JOIN userrole ON roles.roleid = userrole.roleid > > WHERE userrole.userid = 84 OR userrole.userid IS NULL > > > > Spike > > > > Stephen Milligan > > Team Macromedia - ColdFusion > > Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content > > Management' > > http://spikefu.blogspot.com > > > > > -----Original Message----- > > > From: Spike [mailto:[EMAIL PROTECTED]] > > > Sent: 29 January 2003 17:50 > > > To: [EMAIL PROTECTED] > > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > > > > um... > > > > > > I haven't been paying attention to this so far, but from > > > reading the original email wouldn't this do it? > > > > > > SELECT roles.roleid, roles.rolename, users.roleid > > > FROM roles LEFT OUTER JOIN users ON roles.roleid = > > > users.roleid WHERE users.userid = 84 > > > > > > Spike > > > > > > Stephen Milligan > > > Team Macromedia - ColdFusion > > > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > > > Content Management' http://spikefu.blogspot.com > > > > > > > -----Original Message----- > > > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > > > Sent: 29 January 2003 17:45 > > > > To: [EMAIL PROTECTED] > > > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > > > > > > > > > > > > > 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 > > > > > > > > > > > > > It gives me what I could always get (read first email) which > > > > is everything > > > > that links both, BUT it doesn't give me every role. > > > > > > > > Have I stumped everyone on this list? > > > > > > > > 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] > > > -- > ** 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] __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- ** 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]
