Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2012-03-30
Medium Priority
557 Views
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)

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.

- The Lurking LongFist
0
Question by:LongFist
• 3
• 2
• 2
• +1

LVL 9

Expert Comment

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

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
;
``````

HTH
0

LVL 9

Expert Comment

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

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

LVL 9

Expert Comment

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

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

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

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

Question has a verified solution.

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

One of Google's most recent algorithm changes affecting local searches is entitled "The Pigeon Update." This update has dramatically enhanced search inquires for the keyword "Yelp." Google searches with the word "Yelp" included will now yield Yelp a…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month11 days, 5 hours left to enroll