> > > But now I am only interested in the rows that have a user in it > > > twice.
> > > What I need is SELECT * FROM fruitlist WHERE username (exists more > > > than once)
> > > The output I want to create should be
> > > "Anderson chose orange and pear, this needs some action...... "
> > > So my questions is what is the syntax for
> > > SELECT * FROM fruitlist WHERE username (exists more than once)
> > > Thanks for any help in this matter
> > > Garry Jones > > > Sweden
> > Hi, > > How about: > > SELECT * FROM fruitlist WHERE COUNT(username) > 1
> > or
> > SELECT * FROM fruitlist HAVING COUNT(username) > 1
> > R.
> Neither of these will work.
> You mean: > SELECT COUNT(*) AS cnt FROM fruitlist GROUP BY username HAVING cnt > 1
> ==================== > end post > ====================
> Well, no, not really. > Yeah, the count() needs a group by, but not the username:
> > > But now I am only interested in the rows that have a user in it > > > twice.
> I took this as he wants to get all those rows, so grouping on username > wouldnt do it.
If he wants the rows themselves, then just use a self join... SELECT L.* FROM fruitlist L LEFT JOIN fruitlist R ON R.username = L.username AND R.fruit <> L.fruit WHERE R.username IS NOT NULL
> > > But now I am only interested in the rows that have a user in it > > > twice.
> > > What I need is SELECT * FROM fruitlist WHERE username (exists > > > more > > > than once)
> > > The output I want to create should be
> > > "Anderson chose orange and pear, this needs some action...... "
> > > So my questions is what is the syntax for
> > > SELECT * FROM fruitlist WHERE username (exists more than once)
> > > Thanks for any help in this matter
> > > Garry Jones > > > Sweden
> > Hi, > > How about: > > SELECT * FROM fruitlist WHERE COUNT(username) > 1
> > or
> > SELECT * FROM fruitlist HAVING COUNT(username) > 1
> > R.
> Neither of these will work.
> You mean: > SELECT COUNT(*) AS cnt FROM fruitlist GROUP BY username HAVING cnt > > 1
> ==================== > end post > ====================
> Well, no, not really. > Yeah, the count() needs a group by, but not the username:
> > > But now I am only interested in the rows that have a user in it > > > twice.
> I took this as he wants to get all those rows, so grouping on > username > wouldnt do it.
If he wants the rows themselves, then just use a self join... SELECT L.* FROM fruitlist L LEFT JOIN fruitlist R ON R.username = L.username AND R.fruit <> L.fruit WHERE R.username IS NOT NULL
> GROUP BY (any_unique_column_or_expression) would solve that?
> Maybe even a "random" value.
> R.
I dont understand why you need the join? The results are the same, not?
> > > > But now I am only interested in the rows that have a user in it > > > > twice.
> > > > What I need is SELECT * FROM fruitlist WHERE username (exists > > > > more > > > > than once)
> > > > The output I want to create should be
> > > > "Anderson chose orange and pear, this needs some action...... "
> > > > So my questions is what is the syntax for
> > > > SELECT * FROM fruitlist WHERE username (exists more than once)
> > > > Thanks for any help in this matter
> > > > Garry Jones > > > > Sweden
> > > Hi, > > > How about: > > > SELECT * FROM fruitlist WHERE COUNT(username) > 1
> > > or
> > > SELECT * FROM fruitlist HAVING COUNT(username) > 1
> > > R.
> > Neither of these will work.
> > You mean: > > SELECT COUNT(*) AS cnt FROM fruitlist GROUP BY username HAVING cnt > > > 1
> > ==================== > > end post > > ====================
> > Well, no, not really. > > Yeah, the count() needs a group by, but not the username:
> > > > But now I am only interested in the rows that have a user in it > > > > twice.
> > I took this as he wants to get all those rows, so grouping on > > username > > wouldnt do it.
> If he wants the rows themselves, then just use a self join... > SELECT L.* > FROM fruitlist L LEFT JOIN fruitlist R > ON R.username = L.username AND R.fruit <> L.fruit > WHERE R.username IS NOT NULL
> > GROUP BY (any_unique_column_or_expression) would solve that?
> > Maybe even a "random" value.
> > R.
> I dont understand why you need the join? > The results are the same, not?
The same as what? I don't see where you were heading with your suggestions above, can you paste a complete working query?
>>> "GarryJones" <mor...@algonet.se> wrote in message >>> news:5b2dddb5-4b83-4c35-961c-c59bf9cd8b75@s31g2000yqs.googlegroups.com... >>>> If this is my data in table fruitlist with columns username & >>>> fruit >>>> Smith, lemon >>>> Anderson, orange >>>> Henly, apple >>>> Anderson, pear >>>> SELECT * FROM fruitlist >>>> ... gives me the data so I can (with some added code) write the >>>> following output >>>> "Smith chose lemon, Anderson chose orange, Henly chose apple, >>>> Anderson >>>> chose pear" >>>> But now I am only interested in the rows that have a user in it >>>> twice. >>>> What I need is SELECT * FROM fruitlist WHERE username (exists more >>>> than once) >>>> The output I want to create should be >>>> "Anderson chose orange and pear, this needs some action...... " >>>> So my questions is what is the syntax for >>>> SELECT * FROM fruitlist WHERE username (exists more than once) >>>> Thanks for any help in this matter >>>> Garry Jones >>>> Sweden >>> Hi, >>> How about: >>> SELECT * FROM fruitlist WHERE COUNT(username) > 1 >>> or >>> SELECT * FROM fruitlist HAVING COUNT(username) > 1 >>> R. >> Neither of these will work.
>> You mean: >> SELECT COUNT(*) AS cnt FROM fruitlist GROUP BY username HAVING cnt > 1
>> ==================== >> end post >> ====================
>> Well, no, not really. >> Yeah, the count() needs a group by, but not the username:
>>>> But now I am only interested in the rows that have a user in it >>>> twice. >> I took this as he wants to get all those rows, so grouping on username >> wouldnt do it.
> If he wants the rows themselves, then just use a self join... > SELECT L.* > FROM fruitlist L LEFT JOIN fruitlist R > ON R.username = L.username AND R.fruit <> L.fruit > WHERE R.username IS NOT NULL
>> GROUP BY (any_unique_column_or_expression) would solve that?
>If this is my data in table fruitlist with columns username & fruit
>Smith, lemon >Anderson, orange >Henly, apple >Anderson, pear
>SELECT * FROM fruitlist
>... gives me the data so I can (with some added code) write the >following output
>"Smith chose lemon, Anderson chose orange, Henly chose apple, Anderson >chose pear"
>But now I am only interested in the rows that have a user in it twice.
I don't think this is what you want, because there are no rows that have the same user (or any user) in it twice.
>What I need is SELECT * FROM fruitlist WHERE username (exists more >than once)
>The output I want to create should be
>"Anderson chose orange and pear, this needs some action...... "
What do you want your query to do (and the output to be) when your table contains:
Henly, apple Anderson, pear Henly, apple Anderson, pear Henly, apple Anderson, pear
?
Usually, denizens at this group are quite good at asking questions first, before coming up with a proposed query. I wonder what caused the absense of such questions now. Perhaps the fact that the OP hasn't returned to the thread yet?
|
