Solved

mySQL, Self Join Table?

Posted on 2007-12-07
12
815 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 142

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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 250 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 250 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now