0tacon
asked on
mySQL, Self Join Table - more than a single Left Join?
See:
https://www.experts-exchange.com/questions/23008349/mySQL-Self-Join-Table.html
For the basis of this question.
What I'm after now is to ascertain if, and how, it's possible to extend the query:
select
a.asofdate as date,
a.instrticker as hasA,
a.updvalue as aValue,
b.instrticker as hasB,
b.updvalue as bValue
from
transact.nav as a
left join
transact.nav as b
on
(a.asofdate = b.asofdate) and (b.instrticker = 'b')
where
(a.instrticker = 'a');
To include a third variable ('c') which results in two additional columns.
This would thus take data from:
Table: transact.nav
asofdate | instrticker | updvalue
2001 | a | 1
2001 | b | 2
2001 | c | 3
2003 | a | 4
2003 | c | 5
2008 | a | 6
2009 | a | 7
2010 | a | 8
2010 | b | 9
2011 | b | 10
Desired output:
asofdate | instrticker | updvalue| instrticker | updvalue| instrticker | updvalue
2001 | a | 1 | b | 2 | c | 3
2003 | a | 4 | null | null | c | 5
2008 | a | 6 | null | null | null | null
2009 | a | 7 | null | null | null | null
2010 | a | 8 | b | 9 | null | null
Any help, as ever, is greatly appreciated.
https://www.experts-exchange.com/questions/23008349/mySQL-Self-Join-Table.html
For the basis of this question.
What I'm after now is to ascertain if, and how, it's possible to extend the query:
select
a.asofdate as date,
a.instrticker as hasA,
a.updvalue as aValue,
b.instrticker as hasB,
b.updvalue as bValue
from
transact.nav as a
left join
transact.nav as b
on
(a.asofdate = b.asofdate) and (b.instrticker = 'b')
where
(a.instrticker = 'a');
To include a third variable ('c') which results in two additional columns.
This would thus take data from:
Table: transact.nav
asofdate | instrticker | updvalue
2001 | a | 1
2001 | b | 2
2001 | c | 3
2003 | a | 4
2003 | c | 5
2008 | a | 6
2009 | a | 7
2010 | a | 8
2010 | b | 9
2011 | b | 10
Desired output:
asofdate | instrticker | updvalue| instrticker | updvalue| instrticker | updvalue
2001 | a | 1 | b | 2 | c | 3
2003 | a | 4 | null | null | c | 5
2008 | a | 6 | null | null | null | null
2009 | a | 7 | null | null | null | null
2010 | a | 8 | b | 9 | null | null
Any help, as ever, is greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER