cheers paul, I just got it with a simple sub select, but I will probably move it into an inner join.
-----Original Message----- From: Paul Johnston [mailto:[EMAIL PROTECTED]] Sent: 03 February 2003 16:05 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL Fuzz Try this: > a quick test shows this to be incorrect... > > with a table named test, and using the sql below, it will > product an incorrect result set... > > SQL > ==== > SELECT tbl_query.Name, tbl_query.FileID, tbl_query.SharerID, > myTable.Name FROM ( > SELECT Name, FileID, SharerID > FROM myTable) as tbl_query > INNER JOIN myTable ON tbl_query.SharerID = myTable.SharerID SELECT tbl_query.Name, tbl_query.FileID, tbl_query.SharerID, myTable.Name FROM ( SELECT Name, FileID, SharerID FROM myTable) as tbl_query INNER JOIN myTable ON tbl_query.SharerID = myTable.FileID Changing the join to itself to be on the Primary Key (or what I assume is the PK). That will change the resultset, whether it gives you what you want, I'm not sure. Paul > > > Results > ====== > foo 1 2 foo > boo 2 4 boo > goo 3 1 goo > choo 4 3 choo > > > > What it should be is : > ================= > > foo 1 2 boo > boo 2 4 choo > goo 3 1 foo > choo 4 3 goo > > > > > > > -----Original Message----- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 03 February 2003 15:32 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] SQL Fuzz > > > You can join a table to itself... > > SELECT tbl_query.Name, tbl_query.FileID, tbl_query.SharerID, > myTable.Name FROM ( > SELECT Name, FileID, SharerID > FROM myTable) as tbl_query > INNER JOIN myTable ON tbl_query.SharerID = myTable.SharerID > > > yes that would make sense, but there is no other table.... > > there is only one table. > > > > -----Original Message----- > > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > > Sent: 03 February 2003 15:25 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] SQL Fuzz > > > > > > > Here's one I cant seem to get at the mo.. > > > > > > I have a result (SQL Server) which gets me back data all fine and > > > dandy... it returns the following [pseudo] fields... > > > > > > Name > > > FileID > > > SharerID > > > > > > A sample set of data could be : > > > > > > Name FileID SharerID > > > ------- ------- ------------ > > > Foo 1 2 > > > Boo 2 4 > > > Goo 3 1 > > > Choo 4 3 > > > > > > What I need to do is query the table above BUT get the > 'Name' which > > > corresponds to the SharerID itself..in one query..... > > > > SELECT tbl.Name, tbl.FileID, tbl.SharerID, otherTbl.Name > > FROM ( > > SELECT Name, FileID, SharerID > > FROM MyTable) as tbl > > INNER JOIN Othertbl ON tbl.SharerID = Othertbl.SharerID > > > > Make Sense? > > > > 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]
