Trying to work out a join
Trying to work out a join
am 18.12.2004 11:32:17 von JohnD
Im trying to transfer an old asp/SQL server application to asp/MySQL and
have run into a problem with SQL (Or suggest another news group?)
My version of MySQL doesnt seem to support sub queries (and cant upgrade to
the latest that does), so I cant use the existing SQL
Is there any way to join without a subquery?
This lists existing members that are not assigned a file to generate a multi
listbox:
SELECT Members.MemberID, Members.Membername FROM Members WHERE
Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER JOIN
MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
MemberFiles.Filename='assigned.doc')
This lists users that are assigned the file (and doesnt need changing)
SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
Members.MemberID = MemberFiles .MemberID WHERE
MemberFiles.Filename='assigned.doc')
A user can be assigned many files and many users can be assigned many files.
Any ideas for me? :)
Re: Trying to work out a join
am 20.12.2004 11:06:36 von Gary Jones
SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
MemberFiles.FileName IS NULL
"JohnD" wrote in message
news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> Im trying to transfer an old asp/SQL server application to asp/MySQL and
> have run into a problem with SQL (Or suggest another news group?)
> My version of MySQL doesnt seem to support sub queries (and cant upgrade
to
> the latest that does), so I cant use the existing SQL
> Is there any way to join without a subquery?
>
> This lists existing members that are not assigned a file to generate a
multi
> listbox:
>
> SELECT Members.MemberID, Members.Membername FROM Members WHERE
> Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER JOIN
> MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> MemberFiles.Filename='assigned.doc')
>
> This lists users that are assigned the file (and doesnt need changing)
>
> SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> Members.MemberID = MemberFiles .MemberID WHERE
> MemberFiles.Filename='assigned.doc')
>
> A user can be assigned many files and many users can be assigned many
files.
>
> Any ideas for me? :)
>
>
>
Re: Trying to work out a join
am 21.12.2004 03:00:21 von dave
Thanks for the reply - I got that close too.
However, if a user in the Members table has no records in the MemberFiles
table then it doesnt return the user via that query.
eg :
Members :
a, b, c
MemberFiles
a=1.doc
b=2.doc
If I want to query for "2.doc" it should return A, C
Using null doesnt do that
Ta.
"Gary Jones" wrote in message
news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> MemberFiles.FileName IS NULL
>
> "JohnD" wrote in message
> news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > Im trying to transfer an old asp/SQL server application to asp/MySQL
and
> > have run into a problem with SQL (Or suggest another news group?)
> > My version of MySQL doesnt seem to support sub queries (and cant upgrade
> to
> > the latest that does), so I cant use the existing SQL
> > Is there any way to join without a subquery?
> >
> > This lists existing members that are not assigned a file to generate a
> multi
> > listbox:
> >
> > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER JOIN
> > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > MemberFiles.Filename='assigned.doc')
> >
> > This lists users that are assigned the file (and doesnt need changing)
> >
> > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > Members.MemberID = MemberFiles .MemberID WHERE
> > MemberFiles.Filename='assigned.doc')
> >
> > A user can be assigned many files and many users can be assigned many
> files.
> >
> > Any ideas for me? :)
> >
> >
> >
>
>
Re: Trying to work out a join
am 21.12.2004 14:03:56 von Gary Jones
I have to admit to being a little confused now. I thought that the question
was how to replace the query containing the substring, which is what I
supplied. If that wasn't the question, please let me know what was...
"Dave" wrote in message
news:uyoSGDw5EHA.3708@TK2MSFTNGP14.phx.gbl...
>
> Thanks for the reply - I got that close too.
>
> However, if a user in the Members table has no records in the MemberFiles
> table then it doesnt return the user via that query.
>
> eg :
> Members :
>
> a, b, c
>
> MemberFiles
>
> a=1.doc
> b=2.doc
>
> If I want to query for "2.doc" it should return A, C
> Using null doesnt do that
>
> Ta.
>
>
> "Gary Jones" wrote in message
> news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > MemberFiles.FileName IS NULL
> >
> > "JohnD" wrote in message
> > news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > > Im trying to transfer an old asp/SQL server application to asp/MySQL
> and
> > > have run into a problem with SQL (Or suggest another news group?)
> > > My version of MySQL doesnt seem to support sub queries (and cant
upgrade
> > to
> > > the latest that does), so I cant use the existing SQL
> > > Is there any way to join without a subquery?
> > >
> > > This lists existing members that are not assigned a file to generate a
> > multi
> > > listbox:
> > >
> > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
JOIN
> > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > MemberFiles.Filename='assigned.doc')
> > >
> > > This lists users that are assigned the file (and doesnt need changing)
> > >
> > > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > > Members.MemberID = MemberFiles .MemberID WHERE
> > > MemberFiles.Filename='assigned.doc')
> > >
> > > A user can be assigned many files and many users can be assigned many
> > files.
> > >
> > > Any ideas for me? :)
> > >
> > >
> > >
> >
> >
>
>
Re: Trying to work out a join
am 21.12.2004 21:09:34 von snoopy
Thanks for sticking with me - Yes, I want to replace this query, so that
there is no subquery :
SELECT Members.MemberID, Members.Membername FROM Members WHERE
Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
JOIN
MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
MemberFiles.Filename='assigned.doc')
If I use the query above, and using the example below :
Members :
a, b, c
MemberFiles
a=1.doc
b=2.doc
If I want to query for "2.doc" it WILL return A, C (which is what I need)
So, I need a list of ALL distinct members minus the members assigned the
file
Using the query you supplied, it would only return C :
SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
MemberFiles.FileName IS NULL
"Gary Jones" wrote in message
news:OrbXn115EHA.4008@TK2MSFTNGP15.phx.gbl...
> I have to admit to being a little confused now. I thought that the
question
> was how to replace the query containing the substring, which is what I
> supplied. If that wasn't the question, please let me know what was...
>
> "Dave" wrote in message
> news:uyoSGDw5EHA.3708@TK2MSFTNGP14.phx.gbl...
> >
> > Thanks for the reply - I got that close too.
> >
> > However, if a user in the Members table has no records in the
MemberFiles
> > table then it doesnt return the user via that query.
> >
> > eg :
> > Members :
> >
> > a, b, c
> >
> > MemberFiles
> >
> > a=1.doc
> > b=2.doc
> >
> > If I want to query for "2.doc" it should return A, C
> > Using null doesnt do that
> >
> > Ta.
> >
> >
> > "Gary Jones" wrote in message
> > news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> > > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > > MemberFiles.FileName IS NULL
> > >
> > > "JohnD" wrote in message
> > > news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > > > Im trying to transfer an old asp/SQL server application to
asp/MySQL
> > and
> > > > have run into a problem with SQL (Or suggest another news group?)
> > > > My version of MySQL doesnt seem to support sub queries (and cant
> upgrade
> > > to
> > > > the latest that does), so I cant use the existing SQL
> > > > Is there any way to join without a subquery?
> > > >
> > > > This lists existing members that are not assigned a file to generate
a
> > > multi
> > > > listbox:
> > > >
> > > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
> JOIN
> > > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > > MemberFiles.Filename='assigned.doc')
> > > >
> > > > This lists users that are assigned the file (and doesnt need
changing)
> > > >
> > > > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > > > Members.MemberID = MemberFiles .MemberID WHERE
> > > > MemberFiles.Filename='assigned.doc')
> > > >
> > > > A user can be assigned many files and many users can be assigned
many
> > > files.
> > > >
> > > > Any ideas for me? :)
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Trying to work out a join
am 21.12.2004 22:23:25 von snoopy
from a user perspective - this code generates 2 multiple select boxes
left box is a group of (all) members who arent assigned this one file
right box is a group of members who are assigned this one file
Im using javascript to transfer members from one side to another to add or
remove the assignment.
The SQL for the right box is the one that works as we only need this SQL
SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
Members.MemberID = MemberFiles .MemberID WHERE
MemberFiles.Filename='assigned.doc'
The left box uses this SQL as a sub query to exclude memberID's NOT IN that
group (quick, simple and worked ;-)
"dave" wrote in message
news:u6Yvyh55EHA.3376@TK2MSFTNGP12.phx.gbl...
> Thanks for sticking with me - Yes, I want to replace this query, so that
> there is no subquery :
>
> SELECT Members.MemberID, Members.Membername FROM Members WHERE
> Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
> JOIN
> MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> MemberFiles.Filename='assigned.doc')
>
> If I use the query above, and using the example below :
>
> Members :
> a, b, c
>
> MemberFiles
> a=1.doc
> b=2.doc
>
> If I want to query for "2.doc" it WILL return A, C (which is what I need)
> So, I need a list of ALL distinct members minus the members assigned the
> file
>
> Using the query you supplied, it would only return C :
>
> SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> MemberFiles.FileName IS NULL
>
>
>
>
> "Gary Jones" wrote in message
> news:OrbXn115EHA.4008@TK2MSFTNGP15.phx.gbl...
> > I have to admit to being a little confused now. I thought that the
> question
> > was how to replace the query containing the substring, which is what I
> > supplied. If that wasn't the question, please let me know what was...
> >
> > "Dave" wrote in message
> > news:uyoSGDw5EHA.3708@TK2MSFTNGP14.phx.gbl...
> > >
> > > Thanks for the reply - I got that close too.
> > >
> > > However, if a user in the Members table has no records in the
> MemberFiles
> > > table then it doesnt return the user via that query.
> > >
> > > eg :
> > > Members :
> > >
> > > a, b, c
> > >
> > > MemberFiles
> > >
> > > a=1.doc
> > > b=2.doc
> > >
> > > If I want to query for "2.doc" it should return A, C
> > > Using null doesnt do that
> > >
> > > Ta.
> > >
> > >
> > > "Gary Jones" wrote in message
> > > news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> > > > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > > > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > > > MemberFiles.FileName IS NULL
> > > >
> > > > "JohnD" wrote in message
> > > > news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > > > > Im trying to transfer an old asp/SQL server application to
> asp/MySQL
> > > and
> > > > > have run into a problem with SQL (Or suggest another news group?)
> > > > > My version of MySQL doesnt seem to support sub queries (and cant
> > upgrade
> > > > to
> > > > > the latest that does), so I cant use the existing SQL
> > > > > Is there any way to join without a subquery?
> > > > >
> > > > > This lists existing members that are not assigned a file to
generate
> a
> > > > multi
> > > > > listbox:
> > > > >
> > > > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members
INNER
> > JOIN
> > > > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > > > MemberFiles.Filename='assigned.doc')
> > > > >
> > > > > This lists users that are assigned the file (and doesnt need
> changing)
> > > > >
> > > > > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > > > > Members.MemberID = MemberFiles .MemberID WHERE
> > > > > MemberFiles.Filename='assigned.doc')
> > > > >
> > > > > A user can be assigned many files and many users can be assigned
> many
> > > > files.
> > > > >
> > > > > Any ideas for me? :)
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Trying to work out a join
am 22.12.2004 01:03:22 von snoopy
" (quick, simple and worked ;-) "
However - not in MySQL :-/
"dave" wrote in message
news:uudZDL65EHA.1264@TK2MSFTNGP12.phx.gbl...
> from a user perspective - this code generates 2 multiple select boxes
>
> left box is a group of (all) members who arent assigned this one file
> right box is a group of members who are assigned this one file
>
> Im using javascript to transfer members from one side to another to add or
> remove the assignment.
>
> The SQL for the right box is the one that works as we only need this SQL
>
> SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> Members.MemberID = MemberFiles .MemberID WHERE
> MemberFiles.Filename='assigned.doc'
>
> The left box uses this SQL as a sub query to exclude memberID's NOT IN
that
> group (quick, simple and worked ;-)
>
>
>
>
>
> "dave" wrote in message
> news:u6Yvyh55EHA.3376@TK2MSFTNGP12.phx.gbl...
> > Thanks for sticking with me - Yes, I want to replace this query, so that
> > there is no subquery :
> >
> > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
> > JOIN
> > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > MemberFiles.Filename='assigned.doc')
> >
> > If I use the query above, and using the example below :
> >
> > Members :
> > a, b, c
> >
> > MemberFiles
> > a=1.doc
> > b=2.doc
> >
> > If I want to query for "2.doc" it WILL return A, C (which is what I
need)
> > So, I need a list of ALL distinct members minus the members assigned the
> > file
> >
> > Using the query you supplied, it would only return C :
> >
> > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > MemberFiles.FileName IS NULL
> >
> >
> >
> >
> > "Gary Jones" wrote in message
> > news:OrbXn115EHA.4008@TK2MSFTNGP15.phx.gbl...
> > > I have to admit to being a little confused now. I thought that the
> > question
> > > was how to replace the query containing the substring, which is what I
> > > supplied. If that wasn't the question, please let me know what was...
> > >
> > > "Dave" wrote in message
> > > news:uyoSGDw5EHA.3708@TK2MSFTNGP14.phx.gbl...
> > > >
> > > > Thanks for the reply - I got that close too.
> > > >
> > > > However, if a user in the Members table has no records in the
> > MemberFiles
> > > > table then it doesnt return the user via that query.
> > > >
> > > > eg :
> > > > Members :
> > > >
> > > > a, b, c
> > > >
> > > > MemberFiles
> > > >
> > > > a=1.doc
> > > > b=2.doc
> > > >
> > > > If I want to query for "2.doc" it should return A, C
> > > > Using null doesnt do that
> > > >
> > > > Ta.
> > > >
> > > >
> > > > "Gary Jones" wrote in message
> > > > news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> > > > > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > > > > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > > > > MemberFiles.FileName IS NULL
> > > > >
> > > > > "JohnD" wrote in message
> > > > > news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > > > > > Im trying to transfer an old asp/SQL server application to
> > asp/MySQL
> > > > and
> > > > > > have run into a problem with SQL (Or suggest another news
group?)
> > > > > > My version of MySQL doesnt seem to support sub queries (and cant
> > > upgrade
> > > > > to
> > > > > > the latest that does), so I cant use the existing SQL
> > > > > > Is there any way to join without a subquery?
> > > > > >
> > > > > > This lists existing members that are not assigned a file to
> generate
> > a
> > > > > multi
> > > > > > listbox:
> > > > > >
> > > > > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > > > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members
> INNER
> > > JOIN
> > > > > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > > > > MemberFiles.Filename='assigned.doc')
> > > > > >
> > > > > > This lists users that are assigned the file (and doesnt need
> > changing)
> > > > > >
> > > > > > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > > > > > Members.MemberID = MemberFiles .MemberID WHERE
> > > > > > MemberFiles.Filename='assigned.doc')
> > > > > >
> > > > > > A user can be assigned many files and many users can be assigned
> > many
> > > > > files.
> > > > > >
> > > > > > Any ideas for me? :)
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Trying to work out a join
am 22.12.2004 12:24:40 von Gary Jones
Okay then. try this. Not sure if COALESCE is supported, if not, try ISNULL
instead:-
SET NOCOUNT ON
CREATE TABLE #Members (MemberId CHAR(1) NOT NULL, MemberName CHAR(16) NOT
NULL)
CREATE TABLE #MemberFiles (MemberId CHAR(1) NOT NULL, [FileName] CHAR(32)
NOT NULL)
INSERT INTO #Members VALUES('A','Tom')
INSERT INTO #Members VALUES('B','Dick')
INSERT INTO #Members VALUES('C','Harry')
INSERT INTO #MemberFiles VALUES('A','1.doc')
INSERT INTO #MemberFiles VALUES('B','2.doc')
INSERT INTO #MemberFiles VALUES('A','3.doc')
INSERT INTO #MemberFiles VALUES('C','4.doc')
SELECT #Members.MemberId, #Members.MemberName FROM #Members
LEFT OUTER JOIN #MemberFiles ON #MemberFiles.MemberId = #Members.MemberId
WHERE COALESCE(#MemberFiles.FileName,'') <> '2.doc'
GROUP BY #Members.MemberId, #Members.MemberName
DROP TABLE #Members
DROP TABLE #MemberFiles
"dave" wrote in message
news:eLyebk75EHA.2568@TK2MSFTNGP11.phx.gbl...
> " (quick, simple and worked ;-) "
>
> However - not in MySQL :-/
>
>
>
> "dave" wrote in message
> news:uudZDL65EHA.1264@TK2MSFTNGP12.phx.gbl...
> > from a user perspective - this code generates 2 multiple select boxes
> >
> > left box is a group of (all) members who arent assigned this one file
> > right box is a group of members who are assigned this one file
> >
> > Im using javascript to transfer members from one side to another to add
or
> > remove the assignment.
> >
> > The SQL for the right box is the one that works as we only need this SQL
> >
> > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > Members.MemberID = MemberFiles .MemberID WHERE
> > MemberFiles.Filename='assigned.doc'
> >
> > The left box uses this SQL as a sub query to exclude memberID's NOT IN
> that
> > group (quick, simple and worked ;-)
> >
> >
> >
> >
> >
> > "dave" wrote in message
> > news:u6Yvyh55EHA.3376@TK2MSFTNGP12.phx.gbl...
> > > Thanks for sticking with me - Yes, I want to replace this query, so
that
> > > there is no subquery :
> > >
> > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members INNER
> > > JOIN
> > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > MemberFiles.Filename='assigned.doc')
> > >
> > > If I use the query above, and using the example below :
> > >
> > > Members :
> > > a, b, c
> > >
> > > MemberFiles
> > > a=1.doc
> > > b=2.doc
> > >
> > > If I want to query for "2.doc" it WILL return A, C (which is what I
> need)
> > > So, I need a list of ALL distinct members minus the members assigned
the
> > > file
> > >
> > > Using the query you supplied, it would only return C :
> > >
> > > SELECT Members.MemberId, Members.MemberName FROM Members LEFT JOIN
> > > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > > MemberFiles.FileName IS NULL
> > >
> > >
> > >
> > >
> > > "Gary Jones" wrote in message
> > > news:OrbXn115EHA.4008@TK2MSFTNGP15.phx.gbl...
> > > > I have to admit to being a little confused now. I thought that the
> > > question
> > > > was how to replace the query containing the substring, which is what
I
> > > > supplied. If that wasn't the question, please let me know what
was...
> > > >
> > > > "Dave" wrote in message
> > > > news:uyoSGDw5EHA.3708@TK2MSFTNGP14.phx.gbl...
> > > > >
> > > > > Thanks for the reply - I got that close too.
> > > > >
> > > > > However, if a user in the Members table has no records in the
> > > MemberFiles
> > > > > table then it doesnt return the user via that query.
> > > > >
> > > > > eg :
> > > > > Members :
> > > > >
> > > > > a, b, c
> > > > >
> > > > > MemberFiles
> > > > >
> > > > > a=1.doc
> > > > > b=2.doc
> > > > >
> > > > > If I want to query for "2.doc" it should return A, C
> > > > > Using null doesnt do that
> > > > >
> > > > > Ta.
> > > > >
> > > > >
> > > > > "Gary Jones" wrote in message
> > > > > news:ekSq3tn5EHA.2180@TK2MSFTNGP12.phx.gbl...
> > > > > > SELECT Members.MemberId, Members.MemberName FROM Members LEFT
JOIN
> > > > > > MemberFiles ON MemberFiles.MemberId = Members.MemberId WHERE
> > > > > > MemberFiles.FileName IS NULL
> > > > > >
> > > > > > "JohnD" wrote in message
> > > > > > news:OLG2LzO5EHA.2608@TK2MSFTNGP10.phx.gbl...
> > > > > > > Im trying to transfer an old asp/SQL server application to
> > > asp/MySQL
> > > > > and
> > > > > > > have run into a problem with SQL (Or suggest another news
> group?)
> > > > > > > My version of MySQL doesnt seem to support sub queries (and
cant
> > > > upgrade
> > > > > > to
> > > > > > > the latest that does), so I cant use the existing SQL
> > > > > > > Is there any way to join without a subquery?
> > > > > > >
> > > > > > > This lists existing members that are not assigned a file to
> > generate
> > > a
> > > > > > multi
> > > > > > > listbox:
> > > > > > >
> > > > > > > SELECT Members.MemberID, Members.Membername FROM Members WHERE
> > > > > > > Members.MemberID NOT IN (SELECT Members.MemberID FROM Members
> > INNER
> > > > JOIN
> > > > > > > MemberFiles ON Members.MemberID = MemberFiles.MemberID WHERE
> > > > > > > MemberFiles.Filename='assigned.doc')
> > > > > > >
> > > > > > > This lists users that are assigned the file (and doesnt need
> > > changing)
> > > > > > >
> > > > > > > SELECT Members.MemberID FROM Members INNER JOIN MemberFiles ON
> > > > > > > Members.MemberID = MemberFiles .MemberID WHERE
> > > > > > > MemberFiles.Filename='assigned.doc')
> > > > > > >
> > > > > > > A user can be assigned many files and many users can be
assigned
> > > many
> > > > > > files.
> > > > > > >
> > > > > > > Any ideas for me? :)
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>