dcsimg
www.webdeveloper.com
Results 1 to 4 of 4

Thread: SELECT using JOIN, SUBQUERY, UNION?

  1. #1
    Join Date
    Jul 2012
    Posts
    4

    SELECT using JOIN, SUBQUERY, UNION?

    Hi all,

    I have two identical tables each with a single column (id mediumint)
    Table "Bill" has 12 rows: values (A,B,B,C,C,C,C,C,D,D,E,E)
    Table "Mike" has 5 rows : value (A,C,C,D,F)

    I want to query these tables and produce a result like:

    ---Bill----Mike
    A---1-------1
    B---2-------0
    C---5-------2
    D---2-------1
    E---2-------0
    F---0-------1

    This tells me that there are 2 Bs in Bill, and 5 Cs in Bill, and 0 Es in Mike, etc., I have been trying to do a join, but I think I need something else?

    Use count, group by...hmmm

    Thanks in advance for your help,
    Willie

  2. #2
    Join Date
    Jul 2012
    Posts
    4
    So, nearly 100 pair of eyes, not one comment...hmmm Just tell me its to simple or to difficult! Or at least, point me in some direction... Many thanks, WW

  3. #3
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    A small step.

    The following query probably gives erroneous results:
    select Bill.c1 as name,
    count(Bill.c1) as Bill,
    count(Mike.c1 as Mike
    from Bill
    join Mike on Bill.c1=Mike.c1
    group by name
    but report the results if yoiu can.

  4. #4
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    The correst resukt

    Here is a revised in working order query!
    select Mike.c1 as name, count(Mike.c1) as MikeCount,
    T1.Tcount as BillCount
    from Mike
    join
    (select Bill.c1 as Bills,
    count(Bill.c1) as Tcount
    from Bill
    group by Bill.c1) as T1
    on Mike.c1 = T1.Bills
    group by Mike.c1
    Enjoy.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles