Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Kind of simple join question. Not sure if it's that

    Hello,

    the query
    Code:
    Select Year, ID, Count(*)
    From
    dbo.Table
    Where ID like '22%'
    gives me this

    Code:
    Year     ID     Count
    2000     222      2
    2000     223      1
    2001     222      4
    But I need this:

    Code:
    Year     ID     Count
    2000     222      2
    2000     223      1
    2001     222      4
    2001     223      0
    In Words, any ID ever listed in at least one Year should be listed in every Year. What would be the most efficient way for this?

    Thank you!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cross join of distinct years to distinct ids, then left outer join back to data
    Code:
    select y.year
         , i.id
         , count(d.id) 
      from (
           select distinct year 
             from dbo.Table
            where ID like '22%' 
           ) as y
    cross
      join (
           select distinct ID 
             from dbo.Table
            where ID like '22%' 
           ) as i
    left outer
      join dbo.Table as d
        on d.year = y.year
       and d.ID   = i.ID
    group
        by y.year
         , i.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    Well, that's a quick answer Thank you

  4. #4
    Join Date
    Mar 2007
    Posts
    97
    Just one more thing:

    The
    Code:
    Select ... Where plz like ...
    takes lot of time (about 2 minutes) because the where clause is more complex.

    So, to get a result, it takes 2 minutes. If i want it ordered the way I mentioned it will take 2 * (number columns) , and there are about 5 of them.

    Isn't there a way to run the "Select statement" once and then do some "sql ordering"?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by silas
    Isn't there a way to run the "Select statement" once and then do some "sql ordering"?
    yes, it's called the ORDER BY clause



    as for your execution times, you may need to declare some indexes on your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    97
    So I have to rerun the Select on the dbo.Table for every column? No way to do the Distincts and joins on the "Result"?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, could you repeat the question please?

    you said "If i want it ordered the way I mentioned it will take 2 * (number columns) , and there are about 5 of them."

    first of all, you didn't mention anything

    secondly, how many ways do you want to sort the results of the query i gave you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start with:
    Code:
    CREATE TABLE #summary (
       [year]		INT
    ,  [id]			INT
    ,  [c]			INT
       )
    
    INSERT INTO #summary (
       [year], [id], [c]
       ) SELECT [year], [id], Count(*)
          FROM dbo.Table
          WHERE  [id] LIKE '22%'
    
    SELECT y.[year], i.[id], c.c
       FROM SELECT DISTINCT [year]
          FROM #summary) AS y
       CROSS JOIN (SELECT DISTINCT [id]
          FROM #summary) AS i
       LEFT JOIN #summary AS c
          ON (c.[year] = y.[year]
          AND c.[id] = i.[id])
       ORDER BY y.[year], i.[id]
    The temporary table gets the data of interest in just one pass instead of multiple passes, as well as reducing the overall row count for further processing by r937's suggested JOIN.

    If the [id] column is an INT, then no index will help performance. If [id] is a CHAR column, then an index might help a lot.

    -PatP

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    Thank you Pat, the idea of a temporary table is exactly what I was looking for to avoid multiple passes.
    I thought one could have some kind of a virtual temporary table, which does not have to be created before. I mean, if I do a select and get the result, this result is kind of a table, isn't it?
    What I tried first was doing a "With summary as (Select ..." and later do the distincts and joins (what I called ordering before, sorry) on that summary. I hoped, that by this way, there will only be one pass. But that was a wrong assumption. Seems using the name of a with clause in another from clause is just kind of a link to that select in the with and not the result.
    I hope you got me. First, I'm not very good in english, second, I'm new to databases.

    So, till next time ;-)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice one with the temp table, pat

    how did you know the square brackets would not cause a syntax error?

    this is the SQL forum, not the SQL Server forum!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    97
    OMG, a temp table is a virtual table. Sorry, still have to learn a lot

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by silas
    I thought one could have some kind of a virtual temporary table, which does not have to be created before.
    The implementation of "WITH" clauses (CTEs) may differ from one RDBMS to an other. Anyhow, it should be equivalent to a temp table (in terms of performance) if that's more performant than a nested table expression (as in r937's solution).
    At least, that's what a "good" optimizer should find out by itself.
    So the following should be at least as performant as Pat Phelan's solution:
    Code:
    WITH summary (year, id, c) AS
      (SELECT year, id, COUNT(*)
         FROM dbo.Table
        WHERE  id LIKE '22%'),
     y (year) AS (SELECT DISTINCT year FROM summary),
     i (id) AS (SELECT DISTINCT id FROM summary)
    SELECT y.year, i.id, summary.c
      FROM y CROSS JOIN i LEFT OUTER JOIN summary USING (year,id)
    ORDER BY y.year, i.id
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm still wondering what the other 5 columns are, and how the order needs to be varied from the way it was first mentioned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    how did you know the square brackets would not cause a syntax error?
    The use of dbo.Table told me that it had to be either Microsoft or Sybase. When Silas reported timings for the process, I knew that your virtual table syntax had worked, which meant that Silas had to be using Microsoft SQL. Knowing that allowed me to continue down the engine-specific path a bit further than you had initially gone.
    Quote Originally Posted by r937
    this is the SQL forum, not the SQL Server forum!!
    Correct!
    Quote Originally Posted by r937
    i'm still wondering what the other 5 columns are, and how the order needs to be varied from the way it was first mentioned
    Operative word being "think", I think that Silas only had one order in mind, but that wasn't the order provided by the CROSS JOIN query. By using the temp table and applying an ORDER BY to the result set, I think Silas has got what they wanted.
    Quote Originally Posted by silas
    OMG, a temp table is a virtual table. Sorry, still have to learn a lot
    A temp table isn't exactly a virtual table, but it is pretty darned close. Especially when used inside a stored procedure.

    -PatP

  15. #15
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by r937
    i'm still wondering what the other 5 columns are, and how the order needs to be varied from the way it was first mentioned
    What I try is to mimic the functionality of a pivot table. The columns are dimensions.
    Example:
    Code:
    Year      Location    Type     Size      ...   Count(*)   AVG(Price)   AVG(Visits) ...
    2000      123           A        10-15              5         9000        14    
    2000      123           A        15-20            76          5040        99
    2000      123           A        40-80            12          540          7
    2000      123           B        10-15            25          330         22
    2000      123           B        15-20            12          3330          1
    2000      123           B        40-80            NULL       NULL          NULL
    ...
    It's a bit complex. But temp table is a good thing for drill-down and drill-up.
    What I called ordering before has nothing to do whith the order by command. What I meant was the cross join, so the result in excel is shown in a correct way. That means even there is no object e.g. with size 40-80 I want the row to be shown. I need that for the Charts.
    The With clause doesn't work very well in this case (for sql server), because it creates multiple passes. Not just one.

    Quote Originally Posted by Pat Phelan
    I think Silas has got what they wanted.
    Yes we do :-)

Posting Permissions

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