Query Across Multiple Tables

[shaggydoink]

I've interacted with SQL DBs for years in project/product management roles, although admittedly still a newbie when it comes to actually building and manipulating things within SQL directly. I've inherited a project and have spent hours trying to figure something by browsing through user docs and other posts and trying things but am still stumped - hence me calling in the experts! :)

What I am trying to finish building seemed like it would be pretty easy - it's basically a compatibility matrix between products. The information types I am working with are:

> Software Product Class (example: Intuit)
> Software Product (Quickbooks 2008, Quickbooks 2004, etc.)
> 3rd Party Product Class (example: Microsoft)
> 3rd Party Product (example: Windows XP, Word 2007, etc.)
> Integration Type (example: Operating System, Import, Sync, etc.)
> Support Level (example: Supported, Not Supported, etc.)

A sample of the end result of the matrix I'm trying to build would be something like:

Software Product | Int. Type | 3rd Party Product | Support Level
--------------------------------------------------------------------------
Intuit Quickbooks 2008 | Operating System | MS Windows XP | Supported
Intuit Quickbooks 2008 | Import | MS Word 2007 | Supported
Intuit Quickbooks 2004 | Import | MS Word 2007 | Not Supported

So here is what has been built so far specifically as far as tables:

LNProdClass
LNProdClassID (PK, int)
LNProdClassName (varchar (60))

LNProd
LNProdID (PK, int)
LNProdVersion (varchar (60))
LNProdRelease (varchar (60))
LNProdClassID (FK, int) <-- relates to LNProdClass.LNProdClassID

TPProdClass
TPProdClassID (PK, int)
TPProdClassName (varchar (60))

TPProd
TPProdID (PK, int)
TPProdVersion (varchar (60))
TPProdRelease (varchar (60))
TPProdClassID (FK, int) <-- relates to TPProdClass.TPProdClassID

Type
TypeID (PK, int)
TypeName (varchar (60))

SupLevel
SupLevelID (PK, int)
SupLevelName (varchar (60))

CompMap
MapID (PK, int)
LNProdID (FK, int) <-- relates to LNProd.LNProdID
TypeID (FK, int) <-- relates to Type.TypeID
SupLevelID (FK, int) <-- relates to SupLevel.SupLevelID
TPProID (FK, int) <-- relates to TPProd.TPProdID

I obviously have no problem running a query on CompMap, although since it is all ID-based my results obviously come out something like:

Software Product | Int. Type | 3rd Party Product | Support Level
--------------------------------------------------------------------------
1 | 1 | 1 | 1
1 | 2 | 2 | 1
2 | 2 | 2 | 2

What method that should be used to draw the *names* that relate to the *IDs* from the child tables is where I'm lost. I also haven't ruled out the possibility that a different table construct might accomplish this either.

Any suggestions on what to try or what reference material to view to help me figure this out would be greatly appreciated. Thanks in advance!

[ck9663]

Use JOIN. I'm not good at looking at table structure and constructing the query. I'd rather see some sample record of the source tables and your desired output, sorry.

-- CK

[shaggydoink]

Thanks so much for the quick reply CK.

***************
Here are some SELECTS and sample data - the first is the table that is designed to build the final output/matrix
***************

select * from
CompMap
WHERE MapID = 1

MapID LNProdID TypeID SupLevelID TPProID
----------- ----------- ----------- ----------- -----------
1 5 2 1 4


***************
Then here are the pieces/parts that relate to the IDs of the above tabe/sample:
***************

select * from
LNProdClass
WHERE LNProdIDClass = 1

LNProdClassID LNProdClassName
------------- ----------------
1 MyProduct


select * from
LNProd
WHERE LNProdID = 5

LNProdID LNProdVersion LNProdRelease LNProdClassID
----------- ------------- ------------- -------------
5 9.0 SR-2 1


select * from
TPProdClass
WHERE TPProdClassID = 1

TPProdClassID TPProdClassName
------------- ----------------
1 Windows


select * from
TPProd
WHERE TPProdID = 4

TPProdID TPProdVersion TPProdRelease TPProdClassID
----------- ------------- ------------- -------------
4 Vista Enterprise NULL 1


select * from
SupLevel
WHERE SupLevelID = 1

SupLevelID SupLevelName
----------- -------------
1 Certified

select * from
Type
WHERE TypeID = 2

TypeID TypeName
----------- -----------------
2 Operating System


***************
What I'm trying to end up with is data that looks something like this:
***************

--whatever query--

MapID Product Type Support Level 3rd Party Product
----------- ------------------ ----------- ------------- -----------------
1 MyProduct 9.0 SR-2 Operating System Certified Windows Vista Enterprise

[Delerna]

I will start you off and let you complete it

select MapID,LNProdClassName as Product
from CompMap 
join LNProdClass on CompMap.MapID=LNProdClass.LNProdIDClass
 

so just keep addig extra joins and selecting appropriate fields

select MapID,LNProdClassName as Product,otherfield,otherfield
from CompMap 
join LNProdClass on CompMap.MapID=LNProdClass.LNProdIDClass
join nexttable on joinfield=joinfield
join nexttable on joinfield=joinfield
 

[shaggydoink]

I can't thank you enough Delerna! I haven't quite figured out WHY this works - that is to say, the SELECT draws the LNProdClassName field and I thought the only way to do this would be to include the LNProdClass table in the FROM - for example:

select MapID, LNProdClassName as Product
from CompMap, LNProdClass
etc...

But I will give this a shot a post if I have any issues. Thanks again!

I will start you off and let you complete it

select MapID,LNProdClassName as Product
from CompMap 
join LNProdClass on CompMap.MapID=LNProdClass.LNProdIDClass
 

so just keep addig extra joins and selecting appropriate fields

select MapID,LNProdClassName as Product,otherfield,otherfield
from CompMap 
join LNProdClass on CompMap.MapID=LNProdClass.LNProdIDClass
join nexttable on joinfield=joinfield
join nexttable on joinfield=joinfield
 

[shaggydoink]

p.s: works perfectly! Thanks again for your help on this.

I can't thank you enough Delerna! I haven't quite figured out WHY this works - that is to say, the SELECT draws the LNProdClassName field and I thought the only way to do this would be to include the LNProdClass table in the FROM - for example:

select MapID, LNProdClassName as Product
from CompMap, LNProdClass
etc...

But I will give this a shot a post if I have any issues. Thanks again!