Select syntax to find multi entries?

[GarryJones]

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

[Jerry Stuckle]

This is one place where a subselect outdoes a join:

SELECT f1.userid, f1.fruit
FROM fruitlist f1
WHERE f1.userid IN {
SELECT f2.userid
FROM fruitlist f2
GROUP BY f2.userid
HAVING count(f2.userid) > 1);

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Richard]

"GarryJones" <mor...@algonet.se> wrote in message
news:5b2dddb5-4b83-4c35...@s31g2000yqs.googlegroups.com...

Hi,
How about:
SELECT * FROM fruitlist WHERE COUNT(username) > 1

or

SELECT * FROM fruitlist HAVING COUNT(username) > 1

R.

[Andrew C.]

"GarryJones" <mor...@algonet.se> wrote in message
news:5b2dddb5-4b83-4c35...@s31g2000yqs.googlegroups.com...

> If this is my data in table fruitlist with columns username & fruit
>
> Smith, lemon
> Anderson, orange
> Henly, apple
> Anderson, pear

Fruit?

Has someone mentioned the strawberry query yet? ;-)

A.

[toby]

Neither of these will work.

You mean:
SELECT COUNT(*) AS cnt FROM fruitlist GROUP BY username HAVING cnt > 1

[Richard]

"toby" <to...@telegraphics.com.au> wrote in message
news:01dc7da8-83b7-4f6c...@p23g2000vbl.googlegroups.com...

====================
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.

GROUP BY (any_unique_column_or_expression) would solve that?

Maybe even a "random" value.

R.

[toby]

On Nov 5, 2:22 pm, "Richard" <root@localhost> wrote:
> "toby" <t...@telegraphics.com.au> wrote in message

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

[Richard]

"toby" <to...@telegraphics.com.au> wrote in message

news:52c6ae10-6cbc-4476...@h26g2000vbr.googlegroups.com...

I dont understand why you need the join?
The results are the same, not?

R.

[toby]

The same as what? I don't see where you were heading with your
suggestions above, can you paste a complete working query?

>
> R.

[Jerry Stuckle]

Toby,

What happens if you have:

Smith, lemon
Anderson, orange
Henly, apple
Anderson, pear

Anderson, peach
Anderson, kumquat

[Erick T. Barkhuis]

GarryJones:

>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?

--
Erick