CODEWITHSUNDEEP
sqlsql-serverdatabase
chitrakant sahu
chitrakant sahu

Reputation: 211

How to retrieve same column twice with different conditions in same table?

This is my table:

Anganbadi_ID               Food     Month
-------------------------------------------    
1165                       हाँ         1
1165                       हाँ         2
1165                       हाँ         4
1168                       हाँ         4
2032                       नहीं        4
2218                       नहीं        4
2219                       हाँ         4
2358                       नहीं        4
2546                        हाँ        10

there are 4 columns Anganbadi_ID, Food, Month, Year and I want to compare Food column twice based on two different month values.

e.g. if I select Month=4 for first Food (Food-1) column and Month=10 for second Food (Food-2) column, then it should be like following::

Anganbadi_ID            Food-1     Food-2    
------------------------------------------    
1165                       हाँ          NULL 
1168                       हाँ          NULL 
2032                       नहीं        NULL 
2218                       नहीं        NULL 
2219                       हाँ          NULL 
2358                       नहीं        NULL 
2546                     NULL        हाँ

When I'm trying this code

SELECT     
   Anganbadi_ID, Food,
   (SELECT Food
    FROM Anganbadi AS Anganbadi_2
    WHERE (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) 
      AND (Anganbadi_1.Month = 10)
   ) AS 'Food(2)'
FROM Anganbadi AS Anganbadi_1
WHERE (Month = 4)

It shows following results::

Anganbadi_ID              Food-1     Food-2    
--------------------------------------------
1165                       हाँ          NULL 
1168                       हाँ          NULL 
2032                       नहीं        NULL 
2218                       नहीं        NULL 
2219                       हाँ          NULL 
2358                       नहीं        NULL

Please help me as soon as possible....

Upvotes: 6

Views: 59159

Answers (5)

Ashish Negi
Ashish Negi

Reputation: 1

SELECT 
Food
DISTINCT Anganbadi_ID,

(SELECT Food 
 FROM Anganbadi 
 WHERE      (Anganbadi_ID = A.Anganbadi_ID) 
    AND (Month = 4)) AS Food1,

FROM Anganbadi AS A 
WHERE A.Month = 10 OR A.Month = 4

Upvotes: 0

Ian P
Ian P

Reputation: 1724

The key here is the full outer join on ID, the conditions in each sub query can be anything, Full outer joins will give you one row where ID's match and nulls in the opposing columns where they dont. If you want all results even where there food fulfills neither conditions in either month, then do a left join inside one of the sub querys with a select ID from AllFoodsTable

SELECT Food1, Food2, ID
FROM (
  SELECT Food1, ID
  FROM MyTable
  WHERE Month = 4
) Con1
FULL OUTER JOIN (
  SELECT Food2, ID
  FROM MyTable
  WHERE Month = 10
) Con2
ON Con1.ID = Con2.ID

Upvotes: 1

Nalaka526
Nalaka526

Reputation: 11464

SELECT DISTINCT Anganbadi_ID,

(SELECT Food 
 FROM Anganbadi 
 WHERE      (Anganbadi_ID = A.Anganbadi_ID) 
    AND (Month = 4)) AS Food1,

(SELECT Food 
 FROM Anganbadi 
 WHERE      (Anganbadi_ID = A.Anganbadi_ID) 
    AND (Month = 10)) AS Food2

FROM Anganbadi AS A 
WHERE A.Month = 10 OR A.Month = 4

SQL Fiddle

Upvotes: 12

bummi
bummi

Reputation: 27367

SELECT DISTINCT Anganbadi_ID
,(SELECT     Food
FROM          Anganbadi AS Anganbadi_2
WHERE      (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) AND (Month = 4)) AS 'Food(4)'
,(SELECT     Food
FROM          Anganbadi AS Anganbadi_2
WHERE      (Anganbadi_1.Anganbadi_ID = Anganbadi_ID) AND (Month = 10)) AS 'Food(10)'

FROM         Anganbadi AS Anganbadi_1

SQL-Fiddle

Upvotes: 0

CathalMF
CathalMF

Reputation: 10055

SELECT Anganbadi_ID, Food as food1, null as Food2
where Month = 4

UNION ALL

SELECT Anganbadi_ID, null as food1, food as Food2
where Month = 10

Upvotes: 1

Related Questions