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

silas · #1 (**permalink**) 06-22-07, 08:17

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

r937 · #2 (**permalink**) 06-22-07, 08:22

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

silas · #3 (**permalink**) 06-22-07, 08:29

Well, that's a quick answer

Thank you

silas · #4 (**permalink**) 06-22-07, 18:21

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"?

r937 · #5 (**permalink**) 06-22-07, 18:25

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

silas · #6 (**permalink**) 06-22-07, 18:33

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"?

r937 · #7 (**permalink**) 06-22-07, 18:48

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?

Pat Phelan · #8 (**permalink**) 06-23-07, 01:45

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

silas · #9 (**permalink**) 06-23-07, 05:31

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 ;-)

r937 · #10 (**permalink**) 06-23-07, 06:11

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

silas · #11 (**permalink**) 06-23-07, 07:00

OMG, a temp table is a virtual table. Sorry, still have to learn a lot

Peter.Vanroose · #12 (**permalink**) 06-23-07, 09:19

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

r937 · #13 (**permalink**) 06-23-07, 09:45

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

Pat Phelan · #14 (**permalink**) 06-23-07, 10:28

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

silas · #15 (**permalink**) 06-23-07, 11:01

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 :-)

Internet Services

Web Development

Digital Marketing

Consumer Tech