?
Solved

Query 3 tables

Posted on 2007-08-09
6
Medium Priority
?
151 Views
Last Modified: 2013-11-05
Hi - thanks for looking at my question :)

I would like to query 3 tables from 1 SQL Query from my ASP page.

Here is an example of the data...

This table stores the information..
MDM_REQUEST_RESOURCES
Request_ID                  Resource_Type             Resource_Group
45                                5                                     101
45                                6                                     105
45                                8                                     109


This is a lookup table

MDM_LU_RESOURCE_TYPES
Resource_Type                  Description
5                                          Resource 5 Here
6                                          Resource 6 Here
8                                          Resource 8 Here


and finnally another lookup table...

MDM_LU_RESOURCE_GROUPS
Resource_Type        Resource_Group          Description
5                                101                               Resource 5 Here
6                                105                               Resource 6 Here
8                                109                               Resource 8 Here

What I would like to do is to display each entry in MDM_REQUEST_RESOURCES where Request_ID =45
and display the descriptions of the Resource types and Resource Groups.

I have had several goes trying to use SQL joins but to no avail.

Any ideas?

0
Comment
Question by:Pigdogmonster
6 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19663138
SELECT a.RequestID, b.Description AS ResTypeDesc, c.Description AS ResGrpDesc
FROM MDM_REQUEST_RESOURCES a
JOIN MDM_LU_RESOURCE_TYPES b
ON b.Resource_Type = a.Resource_Type
JOIN MDM_LU_RESOURCE_GROUPS c
ON c.Resource_Group = a.Resource_Group
AND c.Resource_Type = b.Resource_Type
 
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19663140
add on

WHERE a.RequestID=45 (of course)...
0
 

Author Comment

by:Pigdogmonster
ID: 19663204
great,

I cant seem to get it working though :(

This is what I have..
Also - how do I reference the each description?

sqltext10="SELECT a.RequestID, b.Description AS ResTypeDesc, c.Description AS ResGrpDesc"
sqltext2=sqltext2 & "FROM MDM_REQUEST_RESOURCES a"
sqltext2=sqltext2 & "JOIN MDM_LU_RESOURCE_TYPES b"
sqltext2=sqltext2 & "ON b.Resource_Type = a.Resource_Type"
sqltext2=sqltext2 & "JOIN MDM_LU_RESOURCE_GROUPS c"
sqltext2=sqltext2 & "ON c.Resource_Group = a.Resource_Group"
sqltext2=sqltext2 & "AND c.Resource_Type = b.Resource_Type"
                  
oRSceNT10.open sqltext10, "dsn=MDM;uid=*;pwd=*"

'start loop                  
do while oRSceNT10.EOF                  


Excuse my SQL skills as you can see I'm not great with SQL! :)



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
Limbeck earned 120 total points
ID: 19664189
sqltext10="SELECT a.RequestID, b.Description AS ResTypeDesc, c.Description AS ResGrpDesc"
sqltext10=sqltext10 & " FROM MDM_REQUEST_RESOURCES a"
sqltext10=sqltext10 & " JOIN MDM_LU_RESOURCE_TYPES b"
sqltext10=sqltext10 & " ON b.Resource_Type = a.Resource_Type"
sqltext10=sqltext10 & " JOIN MDM_LU_RESOURCE_GROUPS c"
sqltext10=sqltext10 & " ON c.Resource_Group = a.Resource_Group"
sqltext10=sqltext10 & " AND c.Resource_Type = b.Resource_Type"
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19668316

>>Also - how do I reference the each description?

the AS keyword provides an alias for the Description columns - call them what you like - I just chose ResTypeDesc and ResGrpDesc for clarity.
these then become the field names in your recordset, so reference them as you would your RequestID...

oRSceNT10.Fields("ResTypeDesc").Value
0
 
LVL 9

Expert Comment

by:danataylor
ID: 19694024
select r.Request_ID,
          r.Resource_Type,
          r.Resource_Group,
          t.description as TYPE_DESCRIPTION,
         g.description as GROUP_DESCRIPTION
from MDM_REQUEST_RESOURCES R,
        MDM_LU_RESOURCE_TYPES T,
        MDM_LU_RESOURCE_GROUPS G
where r.Request_ID = 45
and t.Resource_Type(+) = r.Resource_Type
and g.Resource_Type(+) = r.Resource_Type
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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