Selecting Same Column Twice from a Single Table But With Different Conditions

Selecting same column twice from a single table but with different conditions

You need a self-join; something like:

Select a.ID, a.Name, b.ID as Boss, b.Name as BossName
from Employees A
left join Employees B
on a.Boss = b.ID

Retrieve same column twice with different conditions

You could add another join to the player_match_activity table, or you could change pma.activity_id = '1' to pma.activity_id IN ('1','2') and use CASE expressions to choose the populate the proper columns:

SELECT DISTINCT p.name, pma_goal.time AS  goal, pma_assist.time AS assist 
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma_goal
   ON pma_goal.player_id = p.player_id
   AND pma_goal.activity_id = '1'
   AND pma_goal.match_id = m.match_id    
FULL JOIN player_match_activity pma_assist
   ON pma_assist.player_id = p.player_id
   AND pma_assist.activity_id = '2'
   AND pma_assist.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'

Alternatively:

SELECT p.name, MAX(CASE WHEN pma.activity_id = '1' THEN pma.time END) AS  goal
             , MAX(CASE WHEN pma.activity_id = '2' THEN pma.time END) AS  assist
FROM player p
INNER JOIN player_match pm
   ON p.player_id = pm.player_id
INNER JOIN matches m
   ON m.match_id = pm.match_id
INNER JOIN team_match tm
   ON tm.team_id = p.team_id 
FULL JOIN player_match_activity pma
   ON pma.player_id = p.player_id
   AND pma.activity_id IN ('1','2')
   AND pma.match_id = m.match_id    
WHERE m.match_id = '163'
  AND tm.home_away = 'home'
GROUP BY p.name

Also, not sure you need to be using FULL JOIN here.

Selecting same column twice with different conditions

Use conditional aggregation.

An aggregate function performs a calculation on a set of values and returns a single value.

conditional aggregation is an aggregation on a conditional expression, e.g. the sum of all costs where the correction = 'no'. This is done with a CASE construct inside the aggregation function (SUM here):

select 
  year(date), month(date),
  sum(case when correction = 'no' then cost end) as sum_no,
  sum(case when correction = 'yes' then cost end) as sum_yes,
  sum(cost) as sum_total
from mytable
group by year(date), month(date)
order by year(date), month(date;

SQL: Selecting same Table Column twice based on different WHERE conditions

You've basically got two queries that you then join together on tbl_Abteilungen.Bezeichnung

So like this:

SELECT ISNULL(A.Abteilung, B.Abteilung) AS Abteilung, ISNULL(A.[Offene Abteilungstermine],0) AS [Offene Abteilungstermine], 
    ISNULL(B.[Überfällige Abteilungstermine],0) AS [Überfällige Abteilungstermine]
FROM (SELECT tbl_Abteilungen.Bezeichnung AS Abteilung, 
      COUNT(tbl_Abteilungen.abteilungen_id) AS [Offene Abteilungstermine] 
      FROM tbl_Lieferschein
      INNER JOIN tbl_Positionen ON tbl_Lieferschein.lieferschein_id =           tbl_Positionen.id_lieferschein
      INNER JOIN tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
      INNER JOIN tbl_Abteilungen ON tbl_Positionen_Abteilungen.id_abteilungen = tbl_Abteilungen.abteilungen_id
      WHERE tbl_Positionen_Abteilungen.fertiggestellt = 0 
      AND tbl_Lieferschein.fertiggestellt = 0 
      AND tbl_Lieferschein.gelöscht = 0
      GROUP BY tbl_Abteilungen.Bezeichnung) A
FULL OUTER JOIN
      (SELECT tbl_Abteilungen.Bezeichnung AS Abteilung, COUNT(tbl_Abteilungen.abteilungen_id) AS [Überfällige Abteilungstermine] 
      FROM tbl_Lieferschein
      INNER JOIN tbl_Positionen ON tbl_Lieferschein.lieferschein_id = tbl_Positionen.id_lieferschein
      INNER JOIN tbl_Positionen_Abteilungen ON tbl_Positionen.positionen_id = tbl_Positionen_Abteilungen.id_positionen
      INNER JOIN tbl_Abteilungen ON tbl_Positionen_Abteilungen.id_abteilungen = tbl_Abteilungen.abteilungen_id
      WHERE tbl_Positionen_Abteilungen.fertiggestellt = 0 
      AND tbl_Lieferschein.fertiggestellt = 0 
      AND tbl_Lieferschein.gelöscht = 0 AND tbl_Positionen_Abteilungen.Abteilungstermin < cast(GETDATE() AS DATE)
      GROUP BY tbl_Abteilungen.Bezeichnung) B
ON A.Abteilung = B.Abteilung

i.e. your two queries as sub-queries, with a FULL OUTER JOIN. Note that in the second query I've added tbl_Abteilungen.Bezeichnung AS Abteilung as a SELECT column to make the join possible.

SELECT one column twice from same table with two WHERE conditions SQL SERVER

Depending the situation, there are a few different strategies to conditionally exclude specific column data from the query.

If one is a subset of the other, you can use CASE to exclude unwanted values

SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,PS.ProductQty
   ,CASE 
      WHEN PS.ShipmentDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE()) 
         THEN PS.ProductQty 
    END AS ProductQtyThisMonth
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate > DATEADD(MONTH, -1, GETDATE())

If the where clauses aren't overlapping, then a UNION ALL may be a better choice. You could also OR both conditions together and add a case for each return.

SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,PS.ProductQty AS ProductQtyAncient
   ,NULL AS ProductQtyFuturistic
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate < DATEADD(YEAR, -1, GETDATE())
UNION ALL
SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,NULL AS ProductQtyAncient
   ,PS.ProductQty AS ProductQtyFuturistic
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate > DATEADD(YEAR, 1, GETDATE())

I more commonly see these techniques used with aggregate functions and you mention that this is part of a larger query, so I want to make sure you're aware of that as well.

SELECT
   PS.ProductId
   ,SUM(CASE WHEN PS.ShipmentDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE())
           THEN PS.ProductQty END) AS ProductQtyThisMonth
   ,SUM(CASE WHEN PS.ShipmentDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
           THEN PS.ProductQty END) AS ProductQtyLastMonth
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate BETWEEN DATEADD(MONTH, -1, GETDATE()) 
       AND DATEADD(MONTH, 1, GETDATE())
GROUP BY
   PS.ProductId

select column twice of same table on different condtion Psql

to get the result in a single row, you can use window functions:

select distinct on (project_id)
project_id,
sum(unit_amount) FILTER (where project_type = 'Internal') over(PARTITION BY project_id) as intern, 
sum(unit_amount) FILTER (where project_type = 'Project') over(PARTITION BY project_id) as project
FROM account_analytic_line 

extend with a fitting where clause.

Select Column Twice With Different Conditions

You need join table_1 twice (one for join seller_id and One for join buyer_id)

SELECT 
    a.firstname
    ,a.lastname
    ,c.firstname
    ,c.lastname
    ,b.seller_id
    ,b.buyer_id
    FROM Table_2 as b
    INNER JOIN Table_1 as a ON a.member_id = b.seller_id
    INNER JOIN Table_1 as c On c.member_id = b.buyer_id

---

Related Topics