Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mySQL, Self Join Table?

Posted on 2007-12-07
12
Medium Priority
?
835 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

718 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