Professional Web Applications Themes

How to find nested dupes - MySQL

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

  1. #1

    Default How to find nested dupes

    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 Guest

  2. #2

    Default Re: How to find nested dupes

    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 [url]http://www.dodgeit.com/[/url]
    == ** ## !! ------------------------------------------------ !! ## ** ==
    TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <myaddress>)
    may bypass my spam filter. If it does, I may reply from another address!
    Pedro Graca Guest

  3. #3

    Default Re: How to find nested dupes


    "Pedro Graca" <hexkiddodgeit.com> schreef in bericht
    news:slrnds5qk6.gck.hexkidID-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 Guest

  4. #4

    Default Re: How to find nested dupes

    Jaak wrote:
    >
    > "Pedro Graca" <hexkiddodgeit.com> schreef in bericht
    > news:slrnds5qk6.gck.hexkidID-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 [url]http://www.dodgeit.com/[/url]
    == ** ## !! ------------------------------------------------ !! ## ** ==
    TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <myaddress>)
    may bypass my spam filter. If it does, I may reply from another address!
    Pedro Graca Guest

  5. #5

    Default Re: How to find nested dupes

    On Mon, 9 Jan 2006 22:57:22 +0100, "Jaak" <jaaksjakie.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 :: [email]andyandyh.co.uk[/email] :: [url]http://www.andyh.co.uk[/url]
    [url]http://www.andyhsoftware.co.uk/space[/url] :: disk and FTP usage ysis tool
    Andy Hassall Guest

  6. #6

    Default Re: How to find nested dupes

    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.
    Christopher Pomasl Guest

Similar Threads

  1. Query to show data but ignore dupes
    By Andrew in forum MySQL
    Replies: 3
    Last Post: August 9th, 06:51 PM
  2. Using DW find to find tags not on page
    By ian@uaa in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: August 9th, 05:13 PM
  3. trailing slash issue in Find.find
    By Jeff Mitchell in forum Ruby
    Replies: 0
    Last Post: August 23rd, 11:30 PM
  4. How to do "FIND" on nested subforms.
    By Denny G. in forum Microsoft Access
    Replies: 0
    Last Post: July 30th, 05:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139