Contact Us Today!   |   + 1 (301) 424 3903
spacer



SQL tricks - creative JOINs with legacy data

Posted At : March 21, 2007 4:43 PM | Posted By : Jo Ann Goertner
Related Categories: Success Story, SQL, Tips

Have you ever needed to extract data from old tables and use it in a way the table designer never planned on? This seems to be an all-too-common experience.

Recently, I learned that I could parse a number out of a string field, cast it as an integer, and actually use that result in a JOIN (as if it were a foreign key) to another table's integer primary key. Here is the JOIN part of my query:


...
inner join listings as list
on list.listing_id =
CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )

A few years ago, before I began to understand the power of SQL, I would have done something like this by hand in Excel. Details of the problem and solution are included below if you'd like to read on ...

The Problem

A client asked me to dump some order data out of his database into a "simple" Excel sheet. It might have actually been simple if he had only needed the ledger, order, product and customer records. These were already linked together very logically with foreign key relationships.

However, he also needed specific product details that were stored in another set of relational tables. These tables were again linked to each other. However, this second set of tables was not explicitly linked back to the ledger/order/product set of tables.

The ledger/order/product tables were designed for products in general. We were working with a particular class of products that happened to be web-listing subscriptions. The listings were built separately and stored in a completely different set of tables.

The two sets of tables have a person_id in common, but this was of only limited help since one person can have many orders and also many listings. How could I tell which listings went with each order?

Would I have to try and match them by date? That didn't look promising, since the listing could be created one day and paid for another. It could also be renewed (paid for a second or third time, when it expired).

The Solution

I discovered that the order_product table included a description field, where someone had the foresight to enter the numeric ID of each listing along with the generic product name (like "One Month" or "One Year"). Retrieving the description column, I had string data that looked something like this:


One Quarter, ID:938
One Month, ID:4129
One Month, ID:2138
6 month listing, ID:7183
One Year, ID:8722
One Month, ID:10031
One Quarter, ID:8734
etc ...

The colon made a handy delimiter for extracting the number from the end of each string, no matter how long either section of the string might be. With a little experimentation I found that I could take the order_product.description field and easily

(1) find the position of the colon:


CHARINDEX(':',op.description)

(2) calculate the length of the numeric string I wanted to extract:


LEN(op.description)-CHARINDEX(':',op.description)

(3) parse out the key for each listing:


RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description)))

(4) cast it as an integer and give it an alias:


SELECT CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer ) AS listing_id

Next, I found I could even join on this field to the corresponding records in the listing table as I demonstrated above.

When all was said and done, I had a query that looked something like this (after sparing you about 50% of the crazy-making complexity of this database):


SELECT
    lgr.payment_date,
    lgr.person_id,
    per.first_name,
    per.last_name,
    prod.description as product,
    op.price,
    CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer ) AS listing_id,
    list.title,
    list.city,
    list.state,
    list.country
    
FROM
    ledger lgr
    
    inner join link_order_ledger as lol
    on lol.ledger_id = lgr.ledger_id
    
    inner join order_product as op
    on op.order_id = lol.order_id
    
    inner join product as prod
    on prod.product_id = op.product_id
    
    inner join person as per
    on per.person_id = lgr.person_id
    
    left outer join listings as list
    on list.listing_id = CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )


WHERE op.product_id in (
    SELECT product_id
    FROM product
    WHERE (product_subtype_id = 1000) -- Subscription: Web Listing
    )
    
ORDER BY lgr.payment_date

Now, this is one more trick in my SQL toolbox, for when the next data challenge comes along!

spacer Comments (0) | spacer Print | spacer Send | spacer del.icio.us | spacer Digg It! | spacer Linking Blogs | 3093 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

There are no comments for this entry.

[Add Comment] [Subscribe to Comments]
Archives By Subject
ActionScript (1) [RSS]
AIR (2) [RSS]
Ajax (1) [RSS]
Apollo (3) [RSS]
CFEclipse (3) [RSS]
CFUnited (16) [RSS]
Classes (4) [RSS]
Client Highlights (1) [RSS]
ColdFusion (35) [RSS]
ColdFusion 9 (2) [RSS]
Cool Stuff (3) [RSS]
Culture (6) [RSS]
Development Approach (12) [RSS]
Flex (1) [RSS]
Fusebox (15) [RSS]
Futurology (1) [RSS]
Java (1) [RSS]
JavaScript (1) [RSS]
Load Testing (4) [RSS]
Management (14) [RSS]
MAX (1) [RSS]
MDCFUG Lunch (9) [RSS]
Mindmapping (1) [RSS]
New Intern (0) [RSS]
News (10) [RSS]
Security (2) [RSS]
Server Software (3) [RSS]
Server Tuning (15) [RSS]
Social Media (1) [RSS]
Spiral Web (2) [RSS]
SQL (4) [RSS]
Success Story (10) [RSS]
Technology (5) [RSS]
Tips (15) [RSS]
Trapeze Development (2) [RSS]
Web 2.0 (4) [RSS]
Webmail (4) [RSS]
Whole Brain Development (7) [RSS]
Calendar
<< March 2011 >>
Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    
Latest from CFBloggers.org
April Meeting: Vicky Ryder presents MediaSlurp
Pros and Cons of Deploying ColdFusion Apps as EAR Files?
Just submitted my PlayBook application to Blackberry for approval
Utilizing Multiple CFLocks with the Same Name
Things to know when installing ColdFusion 9 then updating to 9.01
NAVIGATION
Home
test
test
test2
Recent Entries
No recent entries.
Recent Comments

Fusebox still the most used framework
Gavin Baumanis said: Well, since fusebox is the most widely used framework for CFers... and since you seem content on blo... [More]

Fusebox still the most used framework
John Mason said: Naturally, drawing any conclusions from this survey would be rather foolish. This is not scientific ... [More]

Future Webinar Topics (How to cure a slow or crashing ColdFusion server)
Phil S said: Would you kindly post a link to the recorded presentation so that we can view. Thanks! [More]

How to cure slow/crashing ColdFusion servers - Webinar Thurs 12/3/09 1pm EST
Phil S said: Can you please provide a link to the recorded presentation. Many thanks! [More]

How to cure slow/crashing ColdFusion servers - Webinar Thurs 12/3/09 1pm EST
Ajay Sathuluri said: @Nick - It will be recorded; we're testing tomorrow. @Joe - We're planning to have the first 30 min... [More]

RSS

spacer

Search

Subscribe
Enter your email address to subscribe to this blog.

Tags
cfunited coldfusion development approach fusebox management news server tuning success story tips
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1.002. Contact Blog Owner
 
spacer
gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.