Solved

Using SQL: Finding all common elements between units in three tables

Posted on 2012-03-30
10
527 Views
Last Modified: 2012-05-10
Good Day Experts!

     I have an interesting problem that I'm not sure is really solve-able, at least not at my skill level.  The tables have multiple identifier keys because they describe myriad units that are very similar to each other, yet contain little details that make them different.  Here is what the three major tables look like: (and how they relate)

Table Relations Illustration
     The idea is to figure out what all units (for a particular MODEL_CODE) have in common, even if the results do not describe a full, completed unit.  I've got lots of mathematical ideas, but I'm stumped trying to relate the data in the given tables properly.  It's probably possible, I just don't know how to accomplish this.

     And yes, my hope is to understand the SQL statement well enough to (afterward) determine which elements didn't make it into the previous set, and (somehow) separate them off as options.  And I'm sure there will be a few (ancient) units in this database that will not belong to any of the common units - but wouldn't that break the "all common elements" set?

     The [DETAILS] table contains pieces/parts that make up a unit described in [PRODUCT], and the units described in [PRODUCT] are grouped into various MODEL_CODEs at the [MODEL] level.

     I'm trying to say that - for all of the units in [MODEL] group "A" they have these [DETAIL] elements in common.  Elements would be made up of PART_NUM.PART_REV combinations.  You see - I can describe it, I just can't SELECT it - I keep foundering at INNER JOINS between the tables, trying to make the DETAILS elements "stack up" correctly, then relate back to the PRODUCTs, so that I can group them (or filter them) by MODEL_CODE.

     Thanks (in advance) for your timely assistance!

     - The Lurking LongFist
0
Comment
Question by:LongFist
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 9

Expert Comment

by:OCDan
ID: 37789365
Unless I have completely misunderstood what you have said, I can't see why this wouldn't work:
SELECT m.model_code,
         p.prod_name,
         p.prod_desc,
         p.prod_rev,
         d.part_num,
         d.part_rev,
         d.part_desc
FROM model m
              LEFT JOIN product p ON m.model_code = p.model_code
              LEFT JOIN details d ON d.prod_id = p.prod_id
              AND d.prod_name = p.prod_name
              AND d.prod_desc = p.prod_desc
              AND d.prod_rev = p.prod_rev
Order by model_code, prod_name, prod_desc, prod_rev, part_num, part_rev, part_desc

You should get a list like this:

Model_code      Prod_name      Prod_desc      Prod_Rev      part_num      part_rev        part_desc
A                               1                   test                      1                        234              1           test1
A                               1                   test                      1                        235              1           test2
A                               1                   test                      1                        236              1           test3

So you have 1 model, 1 product yet 3 parts in this example. It should find every combination for you.
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 37789385
Not quite sure what do you want.

If you can just create an excel sheet with some example data in all 3 tables and then show us what you want to select, may be we can help better.

From what I understood what you're looking for is a list of PART_NUM.PART_REV for a given MODEL_CODE. If that is so, you can do it like this:

' Following combines all 3 tables into one table containing all the data.
select *
from MODEL m, PRODUCT p, DETAIL d
where
  m.MODEL_CODE = p.MODEL_CODE and
  p.PROD_ID = d.PROD_ID
' add more clauses for name, desc, rev if PROD_ID is not unique within DETAILS
;

' To just get MODEL_CODE and PART_NUM.PART_REV you select from the table above
select m.MODEL_CODE, PART_NUM||'.'||PART_REV
from MODEL m, PRODUCT p, DETAIL d
where
  m.MODEL_CODE = p.MODEL_CODE and
  p.PROD_ID = d.PROD_ID
' add more clauses for name, desc, rev if PROD_ID is not unique within DETAILS
;

Open in new window


HTH
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37789403
Sorry misread it if you just want Models and parts use this:
SELECT m.model_code,
         d.part_num,
         d.part_rev,
         d.part_desc
FROM model m
              LEFT JOIN product p ON m.model_code = p.model_code
              LEFT JOIN details d ON d.prod_id = p.prod_id
              AND d.prod_name = p.prod_name
              AND d.prod_desc = p.prod_desc
              AND d.prod_rev = p.prod_rev
group by  m.model_code, d.part_num, d.part_rev, d.part_desc
Order by model_code, part_num, part_rev, part_desc

On a seperate note isn't 'joining' tables using a ',' and a bunch of where clauses bad practice as it makes the query optimiser do a lot more work?
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 37789414
isn't 'joining' tables using a ',' and a bunch of where clauses bad practice as it makes the query optimiser do a lot more work?
Donno! But frankly if I were the implementor of this query optimizer, I don't see a difference between your (original) query and my. IMHO they amount to same execution. But perhaps the OP can run both and post the exec plans for us. :)
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 9

Expert Comment

by:OCDan
ID: 37789424
I wasn't sure either but I read this and having not seen anyone use that before I wondered if there was a reason for it, that was all:
JOINS vs Multiple Tables on One Line
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37789549
<<The idea is to figure out what all units (for a particular MODEL_CODE) have in common>>
, even if the results do not describe a full, completed unit.  I've got lots of mathematical ideas, but I'm stumped trying to relate the data in the given tables properly.  >>
Please define a unit.  Ideally it is better that you provide a sample OUTPUT of the what you want to display.

<<<<It's probably possible, I just don't know how to accomplish this.>>>>
You need to understand relational model and what the JOIN operator does.
0
 
LVL 1

Author Comment

by:LongFist
ID: 37796259
You need to understand relational model and what the JOIN operator does.

     I have no problem joining all the tables together.  That's not my issue.  My issue was to somehow get the SQL code to SELECT only the [Part_Num], [Part_Rev], [Part_Desc] for a given set of units (described/grouped by [Model_Code]).  I need to figure out "what they all have in common".

     The relationship works like this: for a given [Model_Code] there are several [Product_ID], [Product Name], [Product_Desc], [Product Rev]'s.  It's like an incomplete matrix, where there may be four gizmos with the same [Product_ID] but with different [Product_Name]'s, or different [Product_Rev]'s: you know how engineers love to change things!  So these four elements describe a single unit, and by these four keys we can determine what Parts [Details] go into these units.  My goal is to discover what Parts all of these units for a given [Model_Code] have in common.  Metal work?  Framing?  Power connectors?  Anything at all?  And *that's* where it all falls down: I don't know the proper phrasing to get SQL to look at the rows and rows of data and report back that "{For all units classified as Model_Code 'Clock'} they all have the same Metal Work, the same Framing tools, and they all have the same rear-mounting screws." --- that was totally hypothetical; I really don't know yet - that's why I'm doing this.  It's roots can be found in set theory, and since we're working with finite sets I figured SQL would make short work of it.  I've just got to figure out how.

     Also, this isn't (or shouldn't) be a regularly-occurring process - I'm trying to use it to simplify some other processes, so it will remain a tool in my toolkit, not something that is open to the casual user.

     Heck, if I found that there were no common elements (parts/tooling) for a given group of units described by [Model_Code], I might break it down by [Product_ID], [Product Name], [Product_Desc], [Product Rev]'s, basically stating that for the group defined as [Model_Code] these [Product_ID], [Product Name], [Product_Desc], [Product Rev]'s have 'x' parts in common, and these other [Product_ID], [Product Name], [Product_Desc], [Product Rev]'s have 'y' parts and tooling in common - that sort of thing.

Oh, and thank you for your advice so far: let me take a look at what I've got in light of the current suggestions and see if that gets me any closer to the solution I seek.
0
 
LVL 1

Accepted Solution

by:
LongFist earned 0 total points
ID: 37805739
Okay, I guess the answer to this problem is "it cannot be achieved using SQL as it exists today" without some serious programming.

     I've developed a thick-client app that uses SQL to talk to SQL Server, grab the data, then perform those same set-theory functions that I mentioned earlier.  It took a little massaging, but now it works in all cases, including the case where a unit is unlike (shares no common parts or assemblies) any other unit.  Not the easiest code I've ever written, and I would have liked it to have been accomplished at the SQL Server end, but I suspect that it simply isn't possible at this time.

     In case anyone was thinking "doesn't the unique unit invalidate the 'parts in common to all units' set rule?" - yes it does.  But with some judicious VB.NET coding I was able to further separate the various units into different sub-groups (so now a MODEL_CODE has several sub-genres) that describe a different type or purpose of unit, and then they have parts in common.  It's just a matter of figuring out which branch in the logic tree to split out and follow.  It works now, and that's what counts.

     But once, a long time ago (before I kept multiple copies of copious notes), I thought I had encountered a SQL statement that analyzed a table to find the common elements, and grouped the output accordingly.  I guess I either misunderstood the code, misunderstood the output, or misapprehended the whole process - a distinct possibility, given my SQL skill level back then.

     Of course, there could be an argument that my SQL skills aren't much better now...
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sQL pivot 9 39
SQL Restore Script - Syntax Error 8 70
How to find all values in a column which is not money 25 24
Mssql SQL query 14 26
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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