How to Retrieve Same Column Twice with Different Conditions in Same Table

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.

How to select column twice from the same mysql table?

You just need to JOIN the table twice with different aliases.

Something like:

SELECT p1.lastname, p2.lastname, /* other fields */
FROM CHARTER c
JOIN PILOT p1 ON p1.PilotCode = c.PilotCode
JOIN PILOT p2 on p2.PilotCode = c.CoPilotCode

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag is unique).

Or use joins:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

Select Statement To Retrieve Same Column Twice

I hope this will solve your prblm

SELECT  category_id, 
(Select category_name from table1 where category_id= table2.category_id) as category_name ,
parent_category_id,
(Select category_name from table1 where category_id= table2.parent_category_id)
as parent_category_name from table2

How to retrieve same column twice from one table with Where condition

You should be able to use something like this which incorporates a PIVOT:

select M as Male, 
F as Female
from
(
select event_name, gender,
row_number() over(partition by gender, country order by id) rn
from yourtable
where gender in ('M', 'F')
and country = 12
) src
pivot
(
max(event_name)
for gender in (M, F)
) piv

See SQL Fiddle with Demo

Or you can use an aggregate function with a CASE statement:

select 
max(case when gender = 'M' then event_name end) male,
max(case when gender = 'F' then event_name end) female
from
(
select event_name, gender,
row_number() over(partition by gender, country order by id) rn
from yourtable
where gender in ('M', 'F')
and country = 12
) src
group by rn

See SQL Fiddle with Demo

Both produce the same result:

|       MALE |     FEMALE |
---------------------------
| Cricket | BasketBall |
| BasketBall | Tennis |
| Hockey | Volly Ball |

Count same column twice based on condition

You could use a conditional aggregation

select Amount as amount2
, sum( case when Account_Fkey is not null
and Amount is not null then 1 else 0 end) CountUsed
, sum( case when Account_Fkey is null
and Amount is not null then 1 else 0 end) CountNotUsed
from tblGiftCards
group by Amount


Related Topics



Leave a reply



Submit