I have 3 tables which are following::
Village
Village_ID Village_Name 446261 ??????? 446262 ?????? 446263 ?????????? 446264 ???? 446265 ?????
Anganbadi_Master
Anganbadi_ID Anganbadi_Name Village_ID 1307 ??????? 446260 1308 ??????-2 446260 1309 ??????-1 446262 1310 ??????-2 446262 1311 ??????-3 446262 1312 ??????????-1 446263 1313 ??????????-2 446263
Anganbadi
Anganbadi_ID Food Month Year 1179 ??? 5 2013 1309 ??? 1 2013 1309 ???? 1 2014 1309 ??? 2 2013 1310 ??? 1 2013 1310 ??? 2 2013 1310 ??? 3 2013 1311 ???? 3 2013 2032 ??? 3 2013
Now I want to retreive food column twice in the basis of two different years, where the same month of different years must appear only once, like following::
Anganbadi_ID Month food(2013) food(2014) 1309 1 ??? ???? 1309 2 ??? NULL 1310 1 ??? NULL 1310 2 ??? NULL 1310 3 ??? NULL 1311 3 ???? NULL
But, when I'm trying this code
SELECT DISTINCT Anganbadi.Anganbadi_ID , Anganbadi.Month , Anganbadi.Food AS food2013 , NULL AS Food2014 FROM Anganbadi INNER JOIN Anganbadi_Master ON Anganbadi.Anganbadi_ID = Anganbadi_Master.Anganbadi_ID INNER JOIN Village ON Anganbadi_Master.Village_ID = Village.Village_ID WHERE (Anganbadi.Year = 2013) AND (Anganbadi_Master.Village_ID = 446262) UNION ALL SELECT Anganbadi_1.Anganbadi_ID , Anganbadi_1.Month , NULL AS food2013 , Anganbadi_1.Food AS Food2014 FROM Anganbadi AS Anganbadi_1 INNER JOIN Anganbadi_Master AS Anganbadi_Master_1 ON Anganbadi_1.Anganbadi_ID = Anganbadi_Master_1.Anganbadi_ID INNER JOIN Village AS Village_1 ON Anganbadi_Master_1.Village_ID = Village_1.Village_ID WHERE (Anganbadi_1.Year = 2014) AND (Anganbadi_Master_1.Village_ID = 446262)
It shows following results::
Anganbadi_ID Month food(2013) food(2014) 1309 1 ??? NULL 1309 2 ??? NULL 1310 1 ??? NULL 1310 2 ??? NULL 1310 3 ??? NULL 1311 3 ???? NULL 1309 1 NULL ????
here Anganbadi_ID 1309 showing two different rows for same month (1 ) for year (2013 and 2014)