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
 Data Corruption Issues
 SQL JOIN NULL PROBLEM

Author  Topic 

outlander
Starting Member

1 Post

Posted - 2011-03-09 : 20:29:04
there are Ogrenci,Odev,Sinav,Proje tables and also for connecting to student, Ogr_Sinav, Ogr_Proje, Ogr_Odev tables in sql database.
i wanna that sorting student names in rows, Odev, proje and Sinav in columns.This is why, I tried die to use pivot function.


My code:

DECLARE @SinavSutun VARCHAR(8000)
SELECT @SinavSutun = COALESCE(@SinavSutun + ',[' + cast(SinavAdi as varchar) + ']',
'[' + cast(SinavAdi as varchar)+ ']')
FROM Sinav GROUP BY SinavAdi


DECLARE @OdevSutun VARCHAR(8000)
SELECT @OdevSutun = COALESCE(@OdevSutun + ',[' + cast(OdevAdi as varchar) + ']',
'[' + cast(OdevAdi as varchar)+ ']')
FROM Odev GROUP BY OdevAdi

DECLARE @ProjeSutun VARCHAR(8000)
SELECT @ProjeSutun = COALESCE(@ProjeSutun + ',[' + cast(ProjeAdi as varchar) + ']',
'[' + cast(ProjeAdi as varchar)+ ']')
FROM Proje GROUP BY ProjeAdi

DECLARE @query1 VARCHAR(8000)
SET @query1 = '
Select * from (Select tablo1.OgrenciId from
(
SELECT * FROM
(
SELECT Ogr_Sinav.OgrenciId , Sinav.SinavAdi, Ogr_Sinav.Notu as notu
FROM Ogr_Sinav INNER JOIN
Ogrenci ON Ogr_Sinav.OgrenciId = Ogrenci.OgrenciId INNER JOIN
Sinav ON Ogr_Sinav.SinavId = Sinav.SinavId
) as Sinavlar
PIVOT
(
MAX(notu)
FOR [SinavAdi]
IN (' + @SinavSutun + ')
)
AS p) tablo1
inner join (
------------------------------------------------------------------------------------------------
SELECT * FROM
(
SELECT Ogr_Odev.OgrenciId, Odev.OdevAdi, Ogr_Odev.Notu
FROM Odev INNER JOIN
Ogr_Odev ON Odev.OdevId = Ogr_Odev.OdevId
) as OdevTablo
PIVOT
(
MAX(Notu)
FOR [OdevAdi]
IN (' + @OdevSutun + ')
)
AS p ) tablo2

on

tablo1.OgrenciId=tablo2.OgrenciId ) tablo3

inner join

(
SELECT * FROM
(
SELECT Ogr_Proje.OgrenciId, Proje.ProjeAdi, Ogr_Proje.Notu
FROM Ogr_Proje INNER JOIN
Proje ON Ogr_Proje.ProjeId = Proje.ProjeId
) as ProjeTablo
PIVOT
(
MAX(Notu)
FOR [ProjeAdi]
IN (' + @SinavSutun + ',' + @OdevSutun + ',' + @ProjeSutun + ')
)
AS p
) tablo4
on tablo3.OgrenciId= tablo4.OgrenciId

'


EXECUTE(@query1)

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

when i execute this query, i get rows and columns that i need; but my problem is that, when i use inner join between table1 and table2, it just allows me
select Ogrenci_id but not select * . so Odevs and Sinavs get null.

thanks for your help
   

- Advertisement -