Re: Trying to work out a join

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: dave (none_at_test.com)
Date: 12/21/04 

Date: Wed, 22 Dec 2004 10:23:25 +1300

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" <none@test.com> 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" <gary@vegasolutions.co.uk> 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" <non@nospam.com> 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" <gary@vegasolutions.co.uk> 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" <none@nospam.com> 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? :)
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Why is this query faster?
    ... It's true that in the same way that some problems are easy in MDX that are ... hard in SQL, some problems are hard in MDX that are easy in SQL. ... if the user selects many members on Source you can produce ... for a query that is going to perform reasonably well in all scenarios. ...
    (microsoft.public.sqlserver.olap)
  • Re: Home based Internet research Jobs
    ... We are now hiring home based workers to complete simple online research ... PAY) they are not gonna pay you after you complete your assignment!! ... it is true they pay some of the members.. ...
    (comp.os.vms)
  • RE: Select Query problem
    ... Open the query in design view. ... Next go to View, SQL View ... members after Apr 08 based on the MembershipDate. ... showing up on the report all the members names are showing up. ...
    (microsoft.public.access.queries)
  • Re: functions returning objects
    ... > copies all of the members of that result into the members of objC? ... copy assignment operator which does memberwise assignment is valid. ... The compiler should also check that C's copy constructor is accessible, ... directly in the space allocated for objC, and do no copying at all. ...
    (alt.comp.lang.learn.c-cpp)
  • Re: Uninitialized memory, malloc and unsigned char
    ... undefined behavior even when the member of a struct being assigned ... has a trap representation and it does the member-to-member ... All the standard does is require that structure members be copied by ... something analogous to memcpy rather than member assignment. ...
    (comp.std.c)