inner join sub select error - 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

SQL Server 2008 Forums

Transact-SQL (2008)

inner join sub select error

Author

Topic

webcakali
Starting Member

16 Posts

Posted - 2011-02-20 : 08:17:56

Select
DATEDIFF(d,Uyeler.fldSonGiris,GetDate()) As GecenGun,
Uyeler.fldID,
Uyeler.fldKullAdi,
Uyeler.fldAdSoyad,
Uyeler.fldEposta,
Uyeler.fldSonGiris,
Fotograf.FotoAdet
From tblUyeler As Uyeler
Inner Join
(Select Count(fldID) As FotoAdet,fldKayit As FotoKayit From tblFotograf
Where fldDurum = 1 And fldSil = 0) Fotograf
On Fotograf.FotoKayit >= Uyeler.fldSonGiris
Where
DATEDIFF(d,Uyeler.fldSonGiris,GetDate()) > 30 And
Uyeler.fldDurum = 1 And Uyeler.fldSil = 0 And Uyeler.fldGiris > 10
Order By Uyeler.fldSonGiris DESC

get error

Msg 8120, Level 16, State 1, Line 11
Column 'tblFotograf.fldKayit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

"fldKayit" field on the group doing the same records also repeats

thank you...

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-20 : 09:20:56

Your subquery:

Select
Count(fldID) As FotoAdet,
fldKayit As FotoKayit
From tblFotograf
Where fldDurum = 1 And fldSil = 0

Try:

Select
Count(fldID) As FotoAdet,
fldKayit As FotoKayit
From tblFotograf
Where fldDurum = 1 And fldSil = 0
Group By fldKayit

webcakali
Starting Member

16 Posts

Posted - 2011-02-20 : 09:39:50

I solved this way

Select
Uyeler.fldID,
DATEDIFF(d,Uyeler.fldSonGiris,GetDate()) As GecenGun,
Uyeler.fldKullAdi,
Uyeler.fldAdSoyad,
Uyeler.fldEposta,
Uyeler.fldSonGiris,
(Select Count(fldID) From tblFotograf
Where fldDurum = 1 And fldSil = 0 And fldKayit >= Uyeler.fldSonGiris) As FotoSayisi,
(Select COUNT(fldID) From tblFotoYorum
Where fldDurum = 1 And fldSil = 0 And fldKayit >= Uyeler.fldSonGiris) As YorumSayisi,
(Select COUNT(fldID) From tblUyeler
Where fldDurum = 1 And fldSil = 0 And fldKayit >= Uyeler.fldSonGiris) As UyeSayisi,
(Select COUNT(fldID) From tblMesajlar
Where fldDurum = 1 And fldSil = 0 And fldKayit >= Uyeler.fldSonGiris) As ForumSayisi
From tblUyeler As Uyeler
Where
DATEDIFF(d,Uyeler.fldSonGiris,GetDate()) > 30 And
Uyeler.fldDurum = 1 And Uyeler.fldSil = 0 And Uyeler.fldGiris > 10
Order By Uyeler.fldSonGiris DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources