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)