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
Get Last Friday's Date Unless Today Is Friday Using T-Sql
How to Use SQL Wildcards in Linq to Entity Framework
SQL Server: Any Equivalent of Strpos()
SQL Server Insert into with Where Clause
SQL Server Group by Query Select First Row Each Group
Detect Duplicate Items in Recursive Cte
Two Columns in Subquery in Where Clause
How to Get a Distinct List of Words Used in All Field Records Using Ms SQL
SQL Server 2005: How to Subtract 6 Month
How to Match Multiple Column in a Table with SQLite Fts3
Help with Writing a SQL Query for Nested Sets
SQL Server Unpivot Multiple Columns
SQL Query to Count() Multiple Tables
Sql: Retrieve Only the Records Whose Value Has Changed
Implications of Nvarchar (50) VS Nvarchar (Max)