#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Ottawa
    Posts
    5
    Rep Power
    0

    help: Sub or Nested Query into 1 Query


    Hi,

    I am having difficulty in figureing out how to create ONE query from nested querries. Is there one query that is equivaliant to Query #3?


    Here is the 3 querries i used

    QUERY x1

    SELECT a.Col1 , a.Col2, a.Col3, Count(a.Col) AS CountA
    FROM a
    GROUP BY a.Col1, a.Col2, a.Col3
    ORDER BY a.Col1;



    QUERY x2:
    SELECT a.Col1 , Count(a.Col) AS CountB
    FROM a
    GROUP BY a.Col1
    ORDER BY a.Col1;


    QUERY #3:

    SELECT x1.Col1, x1.Col2, x1.Col3, x1.CountX, x2.CountB
    FROM x1 INNER JOIN x2 ON x1.col1 = x2.col1
    WHERE (CountA<>CountB)


    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274

    Re: help: Sub or Nested Query into 1 Query


    there are actually several ways to do is

    the easiest is just a straightforward substitution of X1 and X2 into X3 --
    Code:
    select x1.col1
         , x1.col2
         , x1.col3
         , x1.counta
         , x2.countb
      from (
           select col1 
                , col2
                , col3
                , count(col) as counta
             from a
           group
               by col1 
                , col2
                , col3
           ) as x1 
    inner
      join ( 
           select col1 
                , count(col) as countb
             from a
           group
               by col1 
           ) as x2
        on x1.col1 = x2.col1
     where counta <> countb
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Ottawa
    Posts
    5
    Rep Power
    0

    THANKS! help: Sub or Nested Query into 1 Query


    Hi,

    Thanks for the quick response.

    I was trying to substitute but forgot to use the AS;

    from (...) AS x1


    Marty

IMN logo majestic logo threadwatch logo seochat tools logo