Good one. I don't know how I missed this either!
Thanks!
"gerald_clark" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> What about
> select distinct a.region, a.city
> from mytable a , mytable b
> where a.region=b.region and a.city <> b.city
>
> Jay Blanchard wrote:
>
> >[snip]
>
[snip]
What about
select distinct a.region, a.city
from mytable a , mytable b
where a.region=b.region and a.city <> b.city
[/snip]
Crud! Standing too close to the forest and forgot about a self join...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
What about
select distinct a.region, a.city
from mytable a , mytable b
where a.region=b.region and a.city <> b.city
Jay Blanchard wrote:
[snip]
Anybody?
I have a simple problem and I'm just wondering the BEST query to
solve
it.
I want to return all the rows of a table whose foreign key
[snip]
Anybody?
> > I have a simple problem and I'm just wondering the BEST query to
solve
it.
> > I want to return all the rows of a table whose foreign key value
exists
> more
> > than once in that table. IE...
> >
> > MyTable
> > Region(foreign key)City
> > EastB
Anybody?
""Jeff Burgoon"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Sorry, I forgot to mention I am using version 4.0.20a (no subqueries
> supported)
>
> ""Jeff Burgoon"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I have a simple problem and I'm just wond
Sorry, I forgot to mention I am using version 4.0.20a (no subqueries
supported)
""Jeff Burgoon"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I have a simple problem and I'm just wondering the BEST query to solve it.
> I want to return all the rows of a table whose foreign key val
I have a simple problem and I'm just wondering the BEST query to solve it.
I want to return all the rows of a table whose foreign key value exists more
than once in that table. IE...
MyTable
Region(foreign key)City
EastBaltimore
EastPhil
o_sessions GROUP BY iuser;
+--+--+
| iuser | isession | ssession
+--+--+
|41 |2 | wanted
|42 |4 | wanted
|43 |6 | wanted
+--+--+
Mike
mysql
- Original Message -
From: "Matthew Scarrow"
Add the sSession field and put in the not wanteds and wanteds you will see that the 3
rows your query returns is the first ones in the list per iUser that are the not
wanteds. The query somehow needs to select the second ones. This is where the problem
comes in.
-- Original Message ---
From: "Robo" <[EMAIL PROTECTED]>
> Well, by now i have found a way to work around the problem,
> still the problem is not solved, you may want continue thinking about it:
>
> start with this:
> iSession iUser sSession
> -- -- -
> 1 41 no
>
Well, by now i have found a way to work around the problem,
still the problem is not solved, you may want continue thinking about it:
start with this:
iSession iUser sSession
-- -- -
1 41 no
2 41 wanted
3
Hi.
On Wed 2002-07-17 at 03:21:34 +0200, [EMAIL PROTECTED] wrote:
> SELECT * FROM O_Sessions AS first, O_Sessions AS second WHERE
> first.iSession=MAX(second.iSession) AND first.iUser=second.iUser
>
> yes, nice idea, but in mySQL, grouping functions like MAX are only
> allowed in SELECT ... FROM
ct: not so simple sql-question?: SELECT iSession FROM O_Sessions
ORDER BY iSession DESC GROUP BY iUser
thank's for your replies, but that did not help :-(
this is the setting (also look below for example table):
O_Sessions contains multilpe recordsets for iUser (let' say iUser=42).
I want t
SELECT * FROM O_Sessions AS first, O_Sessions AS second WHERE
first.iSession=MAX(second.iSession) AND first.iUser=second.iUser
yes, nice idea, but in mySQL, grouping functions like MAX are only allowed in SELECT
... FROM, not
in the WERE part :-(
But i will remember this way to work around the G
Have you tried something like this,
SELECT iSession FROM O_Sessions GROUP BY iUser ORDER BY iSessions DESC;
Mike
- Original Message -
From: "Robo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 16, 2002 3:39 PM
Subject: ??? Simple sql-question
thank's for your replies, but that did not help :-(
this is the setting (also look below for example table):
O_Sessions contains multilpe recordsets for iUser (let' say iUser=42).
I want the last RECORDSET of this iUser=42, not the first.
With GROUP BY, all the various recordsets with iUser=42 ar
Message-
From: Robo [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 16, 2002 12:39 PM
To: [EMAIL PROTECTED]
Subject: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP
BY iUser
I want the latest (highest) iSession to be selected:
SELECT iSession FROM O_Sessions GROUP BY iUser
On 16 Jul 2002, at 21:39, Robo wrote:
> I want the latest (highest) iSession to be selected:
>
> SELECT iSession FROM O_Sessions GROUP BY iUser
>
> Because of GROUP BY, allways the first(!) recordset for iUser is
> selected. But i want the last recordset to be selected :-(
I'm not sure wha
I want the latest (highest) iSession to be selected:
SELECT iSession FROM O_Sessions GROUP BY iUser
Because of GROUP BY, allways the first(!) recordset for iUser is selected.
But i want the last recordset to be selected :-(
How can this be done ?
(mySQL)
(iSession ist the primary key => lat
On Fri, Jan 26, 2001 at 12:42:51PM +1300, Quentin Bennett wrote:
> Hi,
>
> how about
>
> select fname, c1.cname, c2.cname, c3.cname from
> firms,
> cityname as c1,
> cityname as c2,
> cityname as c3
> where
> first.city0 = c1.id and
> first.city1 = c2.id and
> first.city2 = c3.id;
>
> CC'ing
t you made of it, allowing others, like me, to suggest
other solutions.
Regards
Quentin
-Original Message-
From: Gábor Lénárt [mailto:[EMAIL PROTECTED]]
Sent: Friday, 26 January 2001 09:20
To: Gerald L. Clark
Cc: [EMAIL PROTECTED]
Subject: Re: simple SQL question
On Thu, Jan 25, 2001 at
On Thu, Jan 25, 2001 at 12:52:11PM -0600, Gerald L. Clark wrote:
> I would suggest not having 2 cities in your firm record, and making
> fname,city your key.
>
> select * from firms order by fname,city would give you.
> A+B company Dallas
> A+B company London
> New systems Ltd New York
[EMAIL PROTECTED] wrote:
>
> Hi,
>
> Sorry for the possible offtopic question I'm going to ask.
>
> I have got something similar (this is very simplicated situation of
> my problem but this is the core of my headache):
>
> CREATE TABLE cityname (
> id BIGINT NOT NULL AUTO_INCREMEN
Hi,
Sorry for the possible offtopic question I'm going to ask.
I have got something similar (this is very simplicated situation of
my problem but this is the core of my headache):
CREATE TABLE cityname (
id BIGINT NOT NULL AUTO_INCREMENT,
cname CHAR(50),
INDEX i
24 matches
Mail list logo
