Link to home
Start Free TrialLog in
Avatar of 0tacon
0taconFlag for United Kingdom of Great Britain and Northern Ireland

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.
ASKER CERTIFIED SOLUTION
Avatar of imitchie
imitchie
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 0tacon

ASKER

Brilliant, spot on! Many thanks