I tend to find when you have lots of nested joins, it becomes a pain with the syntax.
-----Original Message----- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 31 January 2003 09:46 To: '[EMAIL PROTECTED]' 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] -- ** 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]
