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
Postgresql Query to Return Results as a Comma Separated List
How to Select All Records from One Table That Do Not Exist in Another Table
How to Select Last 5 Rows in a Table Without Sorting
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Addition of Total Hours in SQL Server
How to Use Return Value of Insert...Returning in Another Insert
Reactjs Connection With Database
Mysql Query - Records Between Today and Last 30 Days
How to Select Oldest Date from MySQL
How to Change Column Order in a Table Using SQL Query in SQL Server 2005
A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different
How to Select Three Table With Same Column Name But Different Values
How to Find What Privileges a Group Has in Redshift
How to Combine First Name, Middle Name and Last Name in SQL Server
Insert Value into Table If Conidition Is Met At Least One Time
Multiple Query Same Table But in Different Columns MySQL
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes