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]
