Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mySQL, Self Join Table?

Posted on 2007-12-07
12
Medium Priority
?
845 Views
Last Modified: 2008-02-01
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
Comment
Question by:0tacon
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 11

Expert Comment

by:Sven
ID: 20426812

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20426863
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
 
LVL 12

Author Comment

by:0tacon
ID: 20427121
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 2

Expert Comment

by:jaydm
ID: 20428675
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
 
LVL 12

Author Comment

by:0tacon
ID: 20428705
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
 
LVL 11

Expert Comment

by:Sven
ID: 20428799
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
 
LVL 12

Author Comment

by:0tacon
ID: 20428848
DarthSonic, I get no output, but an error that says:

Unknown column 'instrticker2' in 'field list'
0
 
LVL 11

Accepted Solution

by:
Sven earned 1000 total points
ID: 20428945
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
 
LVL 2

Assisted Solution

by:jaydm
jaydm earned 1000 total points
ID: 20429092
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
 
LVL 2

Expert Comment

by:jaydm
ID: 20429410
Looks like DarthSonic beat me to it.
0
 
LVL 12

Author Comment

by:0tacon
ID: 20429522
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
 
LVL 12

Author Comment

by:0tacon
ID: 20440567
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question