Solved

Can you do an "intra-table join"?

Posted on 2013-05-24
4
244 Views
Last Modified: 2013-06-20
I've inherited a table that contains a list of products. For the sake of simplicity, we'll say the columns are as follows:

ID (the auto-incremented value)
name
description
size
subsetOf
price

The field subsetOf can either be a number that is 0, meaning it's a "base" product, or some other integer, referring to another ID. If it's not 0, it contains a different size and price from the base product with a blank name and description (since those fields should be the same as the base.)

If this were in two separate tables, you could obviously do a join on subsetOf = ID and not have the name or description fields in the second table at all. As it stands, if I do a query for a couple of IDs I may wind up with the following:

ID   name          description             size   subsetOf   price
3    green shirt   a nifty green shirt     L      0          5.00
4                                          XL     3          9.00

Open in new window


Is there a way within the query to get instead:
ID   name          description             size   subsetOf   price
3    green shirt   a nifty green shirt     L      0          5.00
4    green shirt   a nifty green shirt     XL     3          9.00

Open in new window


Thanks!
0
Comment
Question by:SiobhanElara
  • 2
4 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 39194607
Hi Elara,

There are a lot of ways (and reasons) to join tables to themselves.  In your case you probably want something like:

SELECT *
FROM mytable t0
LEFT JOIN mytable t1
  ON t0.id = t1.subsetof

There are potential difficulties though.  If you've got more than one row with a value of subsetof that matches a key, do you want both of those rows to show up (as separate rows) or do you have a means of determining which row to choose?


Kent
0
 

Author Comment

by:SiobhanElara
ID: 39194779
Thank you, Kent! That's pretty much what I was looking for, though I've run into an issue with the execution. This is my actual query:

SELECT t1.product_ID, t2.name, t2.description, t2.size, t2.quantity, t1.price, t1.SKU
FROM tbl_products t1
LEFT JOIN tbl_products t2
ON t1.subset_of = t2.product_ID
      WHERE t1.product_ID = <cfqueryparam value="#SESSION.cart[x].product_ID#" cfsqltype="cf_sql_integer">

This works great provided the item has a subset_of that's not 0. If it's 0, I lose the t2 values because there's obviously no product_ID of 0. Is there a way to "pre-check" if t1.subset_of is 0, or am I going about this entirely wrong?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39194791
When the t2 values are null, you just use the t1 values.

A query to put them into a single column is a bit more complex, but it can certainly be done.


Kent
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39199149
You almost certainly want to redesign the data base with the help of a professional DBA.

Each table.column should have a unique meaning.  The SKU would be different for shirts of different sizes, and since inventories are managed and orders are fulfilled by SKU, it should be present as a canonical element in the article identity, and that means it should be in each row of the table.  There is really no advantage at all to omitting the name and description in rows of similar products.   The ambiguity you describe will lead to all sorts of programming headaches and may make reporting and inventory difficult.  

Make a Google search for the exact phrase Should I Normalize my Database and read the very good arguments on both sides of the question.  The takeaway message is this: Each row of the table should describe one item that is in the inventory.  A separate table should contain the count of items that are available for sale, and they should be joined via the SKU.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

23 Experts available now in Live!

Get 1:1 Help Now