Improve company productivity with a Business Account.Sign Up

x
?
Solved

mySQL, Self Join Table?

Posted on 2007-12-07
12
Medium Priority
?
854 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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…

585 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