I have a query problem.

There can be dupes in machine - no problem
There can be dupes in owner - no problem

but I like to find out whether there are machine dupes of the same owner

this are the contents of the table

owner,machine
--------------------
michael,car
brad,car
jeniffer,laptop
john,car
john,car
john,laptop
brad,laptop


I like to do a query to john with result:
car

If i do the same to brad I get an empty result.

Can anybody help please me with this?

Thanks in advance

Jaak wrote:
> There can be dupes in machine - no problem
> There can be dupes in owner - no problem
>
> but I like to find out whether there are machine dupes of the same owner

<snip content="table contents"/>

> I like to do a query to john with result:
> car
>
> If i do the same to brad I get an empty result.

Why don't you check john and brad and whoever else is in the database at
the same time?

> Can anybody help please me with this?

You want to

*GROUP BY*

the records

*HAVING*

more than 1 machine



mysql> desc foo;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| owner | varchar(10) | YES | | NULL | |
| machine | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

mysql> insert foo values
-> ('michael', 'car'),
-> ('brad', 'car'),
-> ('jeniffer', 'laptop'),
-> ('john', 'car'),
-> ('john', 'car'),
-> ('john', 'laptop'),
-> ('brad', 'laptop');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select owner, machine
-> from foo
-> group by owner, machine
-> having count(machine) > 1;
+-------+---------+
| owner | machine |
+-------+---------+
| john | car |
+-------+---------+
1 row in set (0.02 sec)

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

"Pedro Graca" <hexkid@dodgeit.com> schreef in bericht
news:slrnds5qk6.gck.hexkid@ID-203069.user.individual.net...
> Jaak wrote:
>> There can be dupes in machine - no problem
>> There can be dupes in owner - no problem
>>
>> but I like to find out whether there are machine dupes of the same owner
>
> <snip content="table contents"/>
>
>> I like to do a query to john with result:
>> car
>>
>> If i do the same to brad I get an empty result.
>
> Why don't you check john and brad and whoever else is in the database at
> the same time?
>
>> Can anybody help please me with this?
>
> You want to
>
> *GROUP BY*
>
> the records
>
> *HAVING*
>
> more than 1 machine
No, I want to find the duplicate records where both owner and machine are
the same

Jaak wrote:
>
> "Pedro Graca" <hexkid@dodgeit.com> schreef in bericht
> news:slrnds5qk6.gck.hexkid@ID-203069.user.individual.net...
>> Jaak wrote:
>>> There can be dupes in machine - no problem
>>> There can be dupes in owner - no problem
>>>
>>> but I like to find out whether there are machine dupes of the same owner

>> You want to
>>
>> *GROUP BY*
>>
>> the records
>>
>> *HAVING*
>>
>> more than 1 machine
> No, I want to find the duplicate records where both owner and machine are
> the same

Hmmm ... ignore my english.
Is the query giving you the results you wanted?

Try this for effect:

SELECT owner, machine, count(machine)
FROM foo
GROUP BY owner, machine -- HAVING count(machine) > 1
-- WHERE owner = 'john'
;

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

On Mon, 9 Jan 2006 22:57:22 +0100, "Jaak" <jaak@sjakie.jacques> wrote:

>I have a query problem.
>
>There can be dupes in machine - no problem
>There can be dupes in owner - no problem
>
>but I like to find out whether there are machine dupes of the same owner
>
>I like to do a query to john with result:
>car
>
>If i do the same to brad I get an empty result.

mysql> select * from jaak;
+----------+---------+
| owner | machine |
+----------+---------+
| michael | car |
| brad | car |
| jeniffer | laptop |
| john | car |
| john | car |
| john | laptop |
| brad | laptop |
+----------+---------+
7 rows in set (0.00 sec)

mysql> select machine
-> from jaak
-> where owner = 'john'
-> group by machine
-> having count(*) > 1;
+---------+
| machine |
+---------+
| car |
+---------+
1 row in set (0.03 sec)

mysql> select machine
-> from jaak
-> where owner = 'brad'
-> group by machine
-> having count(*) > 1;
Empty set (0.00 sec)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

On Mon, 23 Jan 2006 23:49:28 +0000, Andy Hassall wrote:

>
> mysql> select machine
> -> from jaak
> -> where owner = 'john'
> -> group by machine
> -> having count(*) > 1;
> +---------+
> | machine |
> +---------+
> | car |
> +---------+
> 1 row in set (0.03 sec)
>

And to find all owners with duplicate machines:

mysql> select * from jaak
-> ;
+----------+---------+
| owner | machine |
+----------+---------+
| michael | car |
| brad | car |
| jeniffer | laptop |
| john | car |
| john | car |
| john | laptop |
| brad | laptop |
| brad | laptop |
+----------+---------+
8 rows in set (0.01 sec)
I added aanother laptop for Brad.

mysql> select owner, machine, count(*)
-> from jaak
-> group by owner, machine
-> having count(*) > 1;
+-------+---------+----------+
| owner | machine | count(*) |
+-------+---------+----------+
| brad | laptop | 2 |
| john | car | 2 |
+-------+---------+----------+
2 rows in set (0.13 sec)


Only the duplicate owner/machine are shown for ALL rows with the number of
duplicates.

Chris
Senior Software Engineer, CA
Always remember, you are unique...just like everyone else.