Select Query for Tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select Query for Tables

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-10-30 : 07:39:43

Hi

I have below scenario

table1

Germany Stuttgart
Germany Wuerzburg
Germany Berlin
Germany Dresden
Germany Duesseldorf
Germany Erfurt
Germany Frankfurt a.M

table2

1 Stuttgart Germany
2 Stuttgart Germany
3 Duesseldorf Germany
4 Stuttgart Germany
5 Frankfurt a.M Germany

I have to get like

Stuttgart Stuttgart(3) ALL(5)
Wuerzburg Wuerzburg(0) ALL(5)
Berlin Berlin(0) ALL(5)
Dresden Dresden(0) ALL(5)
Duesseldorf Duesseldorf(1) ALL(5)
Erfurt Erfurt(0) ALL(5)
Frankfurt a.M Frankfurt a.M(1) ALL(5)

I have put the below query

Select rm.Location,rm.Location + '(' + cast(Count(hj.identifier) as varchar) + ')' AS
LocationCount,'ALL' + '(' + cast(sum(count(hj.identifier)) over() as varchar) + ')' AS AllCount from rm
LEFT OUTER JOIN table2 hj on rm.Country=hj.Country AND rm.Location = hj.Location1 where
rm.Country like '%germany%' GROUP BY rm.Location

But it returns only

Duesseldorf Duesseldorf(1) ALL(5)
Frankfurt a.M Frankfurt a.M(1) ALL(5)
Stuttgart Stuttgart(3) ALL(5)

But i need the below result

Stuttgart Stuttgart(3) ALL(5)
Wuerzburg Wuerzburg(0) ALL(5)
Berlin Berlin(0) ALL(5)
Dresden Dresden(0) ALL(5)
Duesseldorf Duesseldorf(1) ALL(5)
Erfurt Erfurt(0) ALL(5)
Frankfurt a.M Frankfurt a.M(1) ALL(5)

can anyone help me pls for the above scenario

Thanks

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 09:16:36

Not sure what you want but maybe something like

select t1.Location, t1.Country, sum(case when t2.Country is null then 0 else 1 end), t3.tot
from table1
left join table2 t2
on t1.Country=t2.Country
AND t1.Location = t2.Location1
cross join (select tot = count(*) from table2) t3
group by t1.Location, t1.Country

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources