Not getting all rows using my query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Not getting all rows using my query

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-05-25 : 03:19:56

Hi, i have writen one query. In result i want to display all the rows from table1. But still it showing wrong results. My query is as below.

SELECT distinct Table1.*, (select Count(t1.Name) as MyCount from Table1 as t1 where Table1.Name = t1.Name)as Mycount,
case when Table1.Name = Table2.Name
then Table3.NameBreakdown
else 'NF'
end as NameBreakdown
FROM Table1 Left Outer Join
Table2 ON Table1.Name = Table2.Name
Left Outer Join
Table3 ON Table1.Culture = Table3.Culture
Order by Table1.Name

I am getting 308587 rows. The original rows in table are 309197 rows.
If i remove distinct from the query i am getting 321988 rows

G. Satish

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 03:23:30

You mean there are 309197 rows in table1?

try
select count(*)
from (select distinct *
from Table1) a

Looks like you may have duplicates in that table.
You are getting more duplicates from the joins which may be due to duplicartes in Table1 or due to problems with the other tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-05-25 : 03:31:13

Yes, i have duplicates in table1. The total number of rows in table1 is 309197. Using above query i need to display all the rows from table1. Even though if conditions doesn't match. can yuo help in this manner. when i ran your query...
select count(*)
from (select distinct *
from Table1) a

I got 308587 as count

quote:
Originally posted by nr

You mean there are 309197 rows in table1?

try
select count(*)
from (select distinct *
from Table1) a

Looks like you may have duplicates in that table.
You are getting more duplicates from the joins which may be due to duplicartes in Table1 or due to problems with the other tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

G. Satish

hermes
Starting Member

1 Post

Posted - 2009-05-27 : 03:24:50

hi,
maybe you can try..

select table1.*,
b.mycount
from table1 cross apply (select count(*) as mycount
from table2
where table1.name=table2.name) as b

hope this helps.
hermes

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-27 : 03:30:10

If you have duplicates in the table then you will have problems. It means you don't have a relaional database.
Can you either get rid of the duplicates or add a column to make it unique.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 10:09:03

quote:
Originally posted by satish.gorijala

Yes, i have duplicates in table1. The total number of rows in table1 is 309197. Using above query i need to display all the rows from table1. Even though if conditions doesn't match. can yuo help in this manner. when i ran your query...
select count(*)
from (select distinct *
from Table1) a

I got 308587 as count

quote:
Originally posted by nr

You mean there are 309197 rows in table1?

try
select count(*)
from (select distinct *
from Table1) a

Looks like you may have duplicates in that table.
You are getting more duplicates from the joins which may be due to duplicartes in Table1 or due to problems with the other tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

G. Satish

thats because you put distinct. when you remove it you will get all the records which will be more than actual count of table (309197) as you've same matching data rendered for multiple records from first table. which of other tables have many to one relationship with this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources