[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

mySQL, Self Join Table?

Experts,

I am currently have a table with the following format:

col1 (date) |  col2  |  col3
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

Column 1 is a date field, 2 is a classification field and the contents of 3 is irrelevant (but a necessary output)


What I need to do is to get the date from the table where:
column b = 'a' or 'b'
data for column 'b' is only included if it matches an equivalent date for an entry where column b =  'a',
all entries where column b = 'a' are included

So, it needs to create the output (going on the above sample data):

col1 (date) |  col2  |  col3  |  col4  |  col5
2001          |    a     |   1      |    b    |    2
2003          |    a     |   4      |  null   |  null
2008          |    a     |   6      |  null   |  null
2009          |    a     |   7      |  null   |  null
2010          |    a     |   8      |   b     |    9


Its a tad tricky to explain - but is this possible?

If I can give more color to the situation please let me know.

Many thanks,
0
0tacon
Asked:
0tacon
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
SvenTech Lead Web-DevelopmentCommented:

SELECT tbl1.col1, tbl1.col2, tbl1.col3, col4 = tbl2.col2, col5 = tbl2.col3
FROM table tbl1
LEFT JOIN table tbl2 ON tbl2.col1 = tbl1.col1 and tbl2.col2 = 'b'
WHERE tbl1.col2 = 'a'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I suggest this "simpler" way:


select col1
, max(case when col2 = 'a' then col2 else null end) col2
, max(case when col2 = 'a' then col3 else null end) col3
, max(case when col2 = 'b' then col2 else null end) col4
, max(case when col2 = 'b' then col3 else null end) col5
from yourtable
where col2 in ('a', 'b')
group by col1

Open in new window

0
 
0taconAuthor Commented:
DarthSonic- I've tried the solution you propose, substituting the table/column names I have as appropriate but can't seem to get it working.

The table I'm working with is called 'nav' in the database 'transact'. To go back to the original examples with the correct field names:

Current Data:

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
2001          |    a             |   1            |    b            |    2
2003          |    a             |   4            |  null           |  null
2008          |    a             |   6            |  null           |  null
2009          |    a             |   7            |  null           |  null
2010          |    a             |   8            |   b             |    9


Sorry if I didnt make this clear initially..!

Angellll, I haven't tried your way, I'll give it a shot now.

...and thanks for the quick replies!
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
jaydmCommented:
I have a couple questions before I try to answer.

Will there ever be more than one match for the 'instrticker' having the value 'a' in a single year?
Will there ever be more than one match for the 'instrticker' having the value 'b' in a single year?
0
 
0taconAuthor Commented:
jaydm, for the purposes of this example there won't be (the data is actually long date, and only entered weekly, so though the year may be the same, the actual field values wont be)
0
 
SvenTech Lead Web-DevelopmentCommented:
What is the error you get running the following statement? Or is the output wrong?
SELECT t1.asofdate, t1.instrticker, t1.updvalue, instrticker2 = t2.instrticker, updvalue2 = t2.updvalue
FROM nav t1 
LEFT JOIN nav t2 ON t2.asofdate = t1.asofdate AND t2.instrticker = 'b'
WHERE t1.instrticker= 'a'

Open in new window

0
 
0taconAuthor Commented:
DarthSonic, I get no output, but an error that says:

Unknown column 'instrticker2' in 'field list'
0
 
SvenTech Lead Web-DevelopmentCommented:
Try this:
SELECT t1.asofdate, t1.instrticker, t1.updvalue, t2.instrticker AS instrtickerB, t2.updvalue AS updvalueB
FROM nav t1 
LEFT JOIN nav t2 ON t2.asofdate = t1.asofdate AND t2.instrticker = 'b'
WHERE t1.instrticker = 'a'

Open in new window

0
 
jaydmCommented:
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');
0
 
jaydmCommented:
Looks like DarthSonic beat me to it.
0
 
0taconAuthor Commented:
Many thanks indeed guys, I'll give it a shot over the weekend if I get a moment, otherwise will get straight on it Monday morning (sorry for the delay)
0
 
0taconAuthor Commented:
My question has been answered, many thanks!

I've extended the question to accommodate a third variable- and opened a new thread to reflect this, please see:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23012605.html
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now