select left join - 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 left join

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-11-07 : 03:04:17

DECLARE @phone table(
phone int
);
insert into @phone
(phone)
values
(111)
insert into @phone
(phone)
values
(222)
insert into @phone
(phone)
values
(333)

DECLARE @servis table(
phon int ,
servis nvarchar(20)
);
insert into @servis
(phon,servis)
values
(111,'aciq')
insert into @servis
(phon,servis)
values
(111,'bagli')
insert into @servis
(phon,servis)
values
(111,'birbasa')

select phone,2 as qiymet from @phone

phone qiymet
111 2
222 2
333 2

select phon as ph ,count(phon)*0.35 as ser
from @servis group by phon

ph ser
111 1.05

how can I get in Results

phone qiymet ser
111 2 1.05
222 2 0
333 2 0

http://sql-az.tr.gg/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 03:21:06

[code]
select p.phone,2 as qiymet,coalesce(ser,0) as ser
from @phone p
left join (select phon as ph ,count(phon)*0.35 as ser
from @servis group by phon)s
on s.phon = p.phone
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 04:53:28

no need to nested query.


select p.phone, 2 as qiymet, count(s.phon)*0.35 as ser
from @phone p
left join @servis s
on p.phone = s.phon
group by p.phone;

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-11-07 : 06:38:07

thank you very much sigmas and visakh

you is my friend

http://sql-az.tr.gg/

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 07:42:23

you are welcome glad to help

you are my friend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources