a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

-------------------

A trick query.

Hi all...

I would like to know how it is possible to make my problem below all in ONE
query or stored procedure.

I select some rows from a table where the resultset is one column with some
values. Lets say 1, 4 and 7:

row val
1 1
2 4
3 7

Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)

Lets say the other table looks like this:

id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'

in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in column
'b'.

Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in the
table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row shall
be deleted.

Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.

I hope that it is understandable and someone can help.

- rick -So ,basically, you want to have all values from the table a and matching
values (if they exist) from table b? How does this work for you:

select
Table1.val,
5 as CriteriaValue, --probably variable...
OtherTable.id,
OtherTable.Text
from
Table1
left join OtherTable on table1.val = OtherTable.a and OtherTable.b = 5

Now, if this query works, you can delete all values from OtherTable that
have a value b = 5 and id not in the select. You can insert the rows
returned by query if you add filter WHERE otherTable.ID is null.
If this doesnt work for you, please elaborate...

MC

"Rick" <rickcool22@.hotmail.comwrote in message
news:45d71f80$0$90276$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi all...
>
I would like to know how it is possible to make my problem below all in
ONE query or stored procedure.
>
I select some rows from a table where the resultset is one column with
some values. Lets say 1, 4 and 7:
>
row val
1 1
2 4
3 7
>
Then I would like these results to manipulate another table together with
another value (lets say some 'b' with value 5)
>
Lets say the other table looks like this:
>
id a b text
1 1 1 'Some text'
2 1 3 'Some text'
3 1 5 'Some text'
4 4 5 'Some text'
5 7 4 'Some text'
6 2 5 'Some text'
>
in the above example the rows with id 3 and 4 match my criteria because 1
and 4 (and not 7) was in the column 'a' together with the value 5 in
column 'b'.
>
Here comes the tricky part (at least for me):
Now because a row without the 'a' value 7 and the 'b' value 5 existed in
the table I would like to create one row with those values.
Also because the 'b' column did have a the value 5 (the row with id 6)
without any of the 'a' values of 1, 4 and 7 (here 'a' is 2), that row
shall be deleted.
>
Then at last I would like the resultset matching 'a' column of 1, 4 and 7
AND 'b' column 5 as a resultset.
>
I hope that it is understandable and someone can help.
>
- rick -
>

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is big
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:
>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
--
Hugo Kornelis, SQL Server MVP

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

what is the error message|||

I do not see error, in the query analyzer, I set “Results in text” and run the query, I see first 8 records and (1 row(s) affected) message, it should show at least 50 rows. Is this a query analyzer problem?

|||

how about this

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName as nyfield From MyTable

|||

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

|||

i think QA is displaying it in a very long line

have this a try

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

print @.ColList

|||

JIM.H. wrote:

In that case, I see more rows, I am just wondering why I could not get everything although I make @.ColList varchar(5000)

tried to simulate you can only make it until 4000

i think you should make use of cursor

|||

If you are using SQL 2005 please check the following:

Tools -> Options -> Query Results -> Results to Text Maximum number of characters displayed in each column (the default is 256)

Tools -> Options -> Query Results -> Results to Grid Maximum Characters Received Non XML data (the default is 65536)

In SQL 2000 in QA

Tools -> Options -> Results ->Maximum number of characters per column (the default is 256)

You may need to increase these numbers

|||

i tried this one in northwind

use northwind

Declare @.ColList char(8000)
Declare @.CrLf varchar(2)
Select @.CrLf=Char(13) + Char(10)
Select @.coLlist= COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + RTRIM(LTRIM(customerid)) From orders

print @.coLlist
select len(@.collist) as txtlength -<- check this out
select datalength(@.collist) as datalenght <-- and this is

here's the result

4000

8000

|||this worked. Thanks.|||there's a limit of byte per row that can be returned, inserted or updated...it's 8096 if i remember correctly...

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big enough to bring more rows, where might the problem mbe?

Declare @.ColList varchar(2000)

Declare @.CrLf varchar(10)

Select @.CrLf=Char(13) + Char(10)

Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') + MyName From MyTable

Select @.ColList

Did you have any SET ROWCOUNT prior to running this SQL> I ran it on my machine and it worked fine for me.|||

If you are using Query Analyzer to execute the query, please go to Tools menu->Options->switch to Results tab->set the 'Maximum characters per column' to max allowed value 8192, then try again.

If you're using Management Studio and you have set to return result as text, please go to Tools->Options->Query Results->SQL Server->Results to Text->set the 'Maximum number of characters displayed in each column' to 8192

|||Thats right. I had changed mine to 1200 sometime back.

a sql statement

This does not display more than 10 rows from the able, varchar(2000) is big
enough to bring more rows, where might the problem mbe?
Declare @.ColList varchar(2000)
Declare @.CrLf varchar(10)
Select @.CrLf=Char(13) + Char(10)
Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
MyName From MyTable
Select @.ColListworks fine on my end.
JIM.H. wrote:
> This does not display more than 10 rows from the able, varchar(2000) is bi
g
> enough to bring more rows, where might the problem mbe?
> Declare @.ColList varchar(2000)
> Declare @.CrLf varchar(10)
> Select @.CrLf=Char(13) + Char(10)
> Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
> MyName From MyTable
> Select @.ColList|||On Mon, 24 Jul 2006 06:44:02 -0700, JIM.H. wrote:

>This does not display more than 10 rows from the able, varchar(2000) is big
>enough to bring more rows, where might the problem mbe?
>Declare @.ColList varchar(2000)
>Declare @.CrLf varchar(10)
>Select @.CrLf=Char(13) + Char(10)
>Select @.ColList = COALESCE(RTRIM(LTRIM(@.ColList)) + ', ' + @.CrLf, '') +
>MyName From MyTable
>Select @.ColList
Hi Jim,
Since this syntax is not supported, it could be anything. Though I have
to admit that it usually either returns the expected results, or just a
single row. I've just recently had a discussion with Omnibuzz about this
on his blog - check
[url]http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html[/u
rl]
The most likely reasons for seeing just 10 rows are forgetting to undo a
previous SET ROWCOUNT 10, or your front-end tool deciding not to show
all the data in long string columns. If you're using Query Analyzer, you
can control this through Tools / Options / Results / Maximum characters
per column (defaults to 256; maximum is 8192). In SQL Server Management
Studio, you can control this through Tools / Options / Query Results /
SQL Server / Result to Text (or Result to Grid). The maximum is 8192 for
Result to Text and 65535 for Result to Grid, but AFAIK, line feeds mess
up the Results to Grid display.
Hugo Kornelis, SQL Server MVP

A simple calculation ?

I have rows of data in a VB DataGrid with the following fields

Product, Price/Unit, # of Units.

I want to be able to display these fields plus an extended value to calculate the Price/Unit * # of Units.

This seems like it should be easy, I'm just getting a little mixed up with the Syntax.

Any help would be appreciated.

Thanks in advance

tattoo

You are writing the syntax yourself.
select Product, PricePerUnit, NumberOfUnits, PricePerUnit * NumberOfUnits as TotalPrice
from ...|||Works perfectly thank you

A question about execution plans

Hello, I'm using SQL 2000 with the latest updates.
I have a large table Call_Record (5 million rows) that has three indexes:
1. A clustered index on account_no ASC, date_start DESC
2. A non-clustered index on date_end DESC
3. A non-clustered index on call_record_id ASC
I'm querying it for failed calls in the last hour:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
The problem is that the execution plan shows that it is using index 1 to
perform this query, whereas index 2 is clearly the best choice. If I
replace the call to GetUtcDate() with a literal date constant like so:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= '2005/11/23')
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
then it does use index (2) as expected, and executes in a fraction of
the time. My question is, why does it pick the "incorrect" index for
the first query, and is there any way to force it to pick index (2)?
MikeMike
I tried to rewrite a little bit your SELECT
DBCC FREEPROCCACHE
GO
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE c.date_end >=dateadd(hour,-1,GetUtcDate()) and c.date_end <
dateadd(day,+1,GetUtcDate()) --replace with the date that is relevant for
the searching
(GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
Do you see now any changes in the execution plan , I'd put the CI on
date_end column since your criteria is based on range date seraching and CI
is probably a good choice for it, but you'll have to test it.
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:%23ibexQI8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Hello, I'm using SQL 2000 with the latest updates.
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of the
> time. My question is, why does it pick the "incorrect" index for the
> first query, and is there any way to force it to pick index (2)?
> Mike|||Mike Chamberlain (none@.hotmail.com) writes:
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of
> the time. My question is, why does it pick the "incorrect" index for
> the first query, and is there any way to force it to pick index (2)?
When making the choice between scanning a clustered index, or using a
non-clustered index + bookmark lookup, the optimizer always have a
delicate choice. If the condition on the column in the NC-index hits
few rows is small, the NC index is good. But if the condition hits many
rows, the NC index is a lot worse than the table scan, as SQL Server
would have to access many data pages more than once.
To determine which to use, SQL Server makes estimates from statistics
saved for the table. When you put in a date literal, SQL Server can see
that the query will only hit a small number of rows, and thus the index
is good.
But for the first query, the problem is that getutcdate() is a non-
deterministic function, and thus will return different values each
time. I guess, therefore, the optimizer does not care about the
expression, but uses the clustered index instead. Since you have a
condition with >= there could potentially be many rows that are
hit in the condition.
In a situation like this an index hint may be a good idea:
FROM dbo.Call_Record c WITH (INDEX = DateEnd_ix)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

A particular row to be bold?

I am retrieving the rows and displaying them in table details of my report. The last row is the weighted mean and needs to be calculated in the stored procedure.

Now, I want to make this particular row in bold font. Is it possible? Please let me know.

Any help will be greatly appreciated !!!

You can select the entire table row and apply style property settings for the entire row. Assuming you have a column in your dataset that distinguishes the real "detail" rows from the weighted mean row, you could write an expression that uses this field to determine if a row should be shown normal or bold.

-- Robert

A little script help

Using the data below as an example I am looking for help with script
that will return all rows of data where neither Field A or B are not 0
or Null

NameAB
John2
John1
John0
John
Ste1
Ste
Paul5
Paul
Paul0

Regards,
CiarnDo you really mean where EITHER A or B are not 0 or not NULL? Try:

WHERE A>0 OR B>0

conversely:

WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL

--
David Portas
SQL Server MVP
--|||I tried your suggestions without success.
Using the data above, I want to return.

Name A B
John 2
John 1
Ste 1
Paul 5

Regards,
Ciarn|||The following should work:

WHERE ISNULL(A, 0) <> 0 OR ISNULL(B, 0) <> 0

-Tom.|||This is where it helps if you include CREATE TABLE and INSERT
statements with your question. The following works for me:

CREATE TABLE YourTable (name VARCHAR(10), a INTEGER NULL, b INTEGER
NULL /* PRIMARY KEY ? UNSPECIFIED */)

INSERT INTO YourTable (name,a,b)
SELECT 'John', 2 , NULL UNION ALL
SELECT 'John', NULL, 1 UNION ALL
SELECT 'John', 0 , NULL UNION ALL
SELECT 'John', NULL, NULL UNION ALL
SELECT 'Ste', NULL, 1 UNION ALL
SELECT 'Ste', NULL, NULL UNION ALL
SELECT 'Paul', 5 , NULL UNION ALL
SELECT 'Paul', NULL, NULL UNION ALL
SELECT 'Paul', NULL, 0

SELECT name, a, b
FROM YourTable
WHERE A>0 OR B>0

Result:

name a b
---- ---- ----
John 2 NULL
John NULL 1
Ste NULL 1
Paul 5 NULL

What did you do differently and what result did you get?

Does this table have a primary key? It should do, and it helps if you
specify the key when you post a question.

--
David Portas
SQL Server MVP
--|||Perfect.
Cheers|||For clarity sake try:

WHERE ISNULL(A,0) <> 0
AND ISNULL(B,0) <> 0

GeoSynch

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113296365.931060.144050@.f14g2000cwb.googlegr oups.com...
> Do you really mean where EITHER A or B are not 0 or not NULL? Try:
> WHERE A>0 OR B>0
> conversely:
> WHERE NULLIF(A,0) IS NULL AND NULLIF(A,0) IS NULL
> --
> David Portas
> SQL Server MVP
> --

A JOIN gives me more rows than I expected

Hello,
I've two tables AZ01 and AZ02 the structure is the same.
Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
I expect that the maximum number of rows is 1.681.000, but at the end I
get 1.684.000 rows!!! How is it possible?
Thanks for your help!
Perhaps SELECT COUNT(DISTINCT col)
"Andrea" <andy@.foo.com> wrote in message
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!
|||If the Key you reference is not unique in the Table AZ02 you will get those
"doubles". It will count the number of rows in the results which match which
each other.
Have a look at this example:
Use Northwind
GO
Select count(Orders.OrderID) From Orders
--> 830
Select count(Orders.OrderID) From Orders inner join [Order Details] OD ON
OD.OrderID = Orders.OrderId
--> 2155
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Andrea" <andy@.foo.com> schrieb im Newsbeitrag
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!

A JOIN gives me more rows than I expected

Hello,
I've two tables AZ01 and AZ02 the structure is the same.
Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
I expect that the maximum number of rows is 1.681.000, but at the end I
get 1.684.000 rows!!! How is it possible?
Thanks for your help!Perhaps SELECT COUNT(DISTINCT col)
"Andrea" <andy@.foo.com> wrote in message
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!|||If the Key you reference is not unique in the Table AZ02 you will get those
"doubles". It will count the number of rows in the results which match which
each other.
Have a look at this example:
Use Northwind
GO
Select count(Orders.OrderID) From Orders
--> 830
Select count(Orders.OrderID) From Orders inner join [Order Details] OD O
N
OD.OrderID = Orders.OrderId
--> 2155
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Andrea" <andy@.foo.com> schrieb im Newsbeitrag
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!

A JOIN gives me more rows than I expected

Hello,
I've two tables AZ01 and AZ02 the structure is the same.
Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
I expect that the maximum number of rows is 1.681.000, but at the end I
get 1.684.000 rows!!! How is it possible?
Thanks for your help!Perhaps SELECT COUNT(DISTINCT col)
"Andrea" <andy@.foo.com> wrote in message
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!|||If the Key you reference is not unique in the Table AZ02 you will get those
"doubles". It will count the number of rows in the results which match which
each other.
Have a look at this example:
Use Northwind
GO
Select count(Orders.OrderID) From Orders
--> 830
Select count(Orders.OrderID) From Orders inner join [Order Details] OD ON
OD.OrderID = Orders.OrderId
--> 2155
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Andrea" <andy@.foo.com> schrieb im Newsbeitrag
news:e0rg1KkRFHA.1208@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've two tables AZ01 and AZ02 the structure is the same.
> Table AZ01 has 1.681.000 rows and AZ02 has 1.700.000 rows.
> I execute the following select: SELECT COUNT(AZ01.COD) AS Conteggio FROM
> AZ01 INNER JOIN AZ02 ON AZ01.COD=AZ02.COD
> I expect that the maximum number of rows is 1.681.000, but at the end I
> get 1.684.000 rows!!! How is it possible?
> Thanks for your help!