SELECT
t1.id
FROM
table1 t1,
table2 t2a,
table2 t2b
WHERE
t1.StopTime = 0 AND
(t2a.Group != 'Dedicated' AND t2a.UserName != t2b.Username) AND
t2a.UserName = t1.UserName
Not sure if you need those () though.
On 2 Feb 2001, Drew Wilder-Goodwin wrote:
> So that looks like it does a select for all in table2 who DO have
> dedicated and just excludes those matches... thats an interesting way of
> doing it.. but that would still return a row for the other groups that
> the username belongs to in table2 right? so for instance if there is 2
> entries in table 2 :
>
> id UserName Group
> 1 test Dedicated
> 2 test Limit1
>
> Then it would match the second one, therefore the id that matched that
> username in table1 would be selected, or am i looking at it
> incorrectly.. Basically, I dont want any rows selected from table one if
> the username has an entry in table2 with a group of Dedicated..
>
> I just ran that query on our database too and it appears to be stuck :\
>
>
> On 02 Feb 2001 16:26:41 -0500, Johnson, Gregert wrote:
> > SELECT t1.id FROM table 1 t1
> > LEFT JOIN table2 t2 ON t1.UserName = t2.UserName
> > AND t2.Group = 'Dedicated'
> > AND t1.StopTime = 0
> > WHERE t2.id IS NULL;
> >
> > --Greg Johnson
> >
> > -----Original Message-----
> > From: Drew [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, February 02, 2001 4:00 PM
> > To: [EMAIL PROTECTED]
> > Subject: Efficient Select
> >
> > Hi I was wondering if anyone could help me find the most efficient..
>or
> > at least A efficient way to preform the following task:
> >
> > I have a table that has some data, one of which is a UserName
>column. I
> > have another table that has multiple rows for that UserName. So
>table
> > one looks like:
> >
> > id int
> > UserName varchar
> > StopTime datetime
> >
> > and table two looks like
> >
> > id int
> > UserName varchar
> > Group varchar
> >
> > So far I have
> >
> > SELECT table1.id
> > FROM table1, table2
> > WHERE table1.StopTime = 0 AND
> > table1.UserName = table2.username AND
> > table2.Group != 'Dedicated'
> >
> > You can see that I am trying to get the id's from the first table
>where
> > the stoptime is 0 and they do NOT have an entry in table2 that says
> > dedicated. There are multiple entries is table2 for each username...
> > Any suggestions?
> >
> > Thanks
> > Drew
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try:
>http://lists.mysql.com/php/unsubscribe.php
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php