0
Answer

How to retrieve same column twice with different conditions

chitrakant

chitrakant

11y
987
1

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)

Next Recommended Forum