• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

SQL: getting info from child > grandparent

hope my title made sense. anyway, i have a database that has a child to grandparent( <-- is that right?: grandparent?)  any way, here's my tables layout.


JobTypeID        JobType                  <------------------ JobTypes  table
------------      --------------      
       1              Roofing
       2              Siding

subTypeID        subType          JobTypeID         <----- SubTypes Table
------------      --------------      ------------
       1              Home                   1
       2              Garage                 1
       3              Home                   2
       4              Garage                 2

OptionID         subTypeID        optionName       <----- Options Table
------------      --------------     ----------------
       1                 1                 Removal of the roof
       2                 1                 Remove Wood
       3                 2                 Removal of the roof
       4                 2                 Remove Wood


*********** TABLES FOR INVOICING *******************

CustomerID        Name           <------------------------- Customers Table
------------       --------------  
       1                 John Smith


projectID              CustomerID          <----------------- Projects Table
-----------           -------------------
    1                           1


projectID             OptionID          <--------------------- Items Table
------------          ----------------
       1                     1
       1                     2
       1                     3

According to the Invoice Table I want this to display my information on a page like this

Roofing (Home)
    - Removal of the roof
    - Remove Wood

Roofing (Garage)
    - Removal of the roof

So if i want to see the information for customer 1, for his first invoice, how do i display this information using the sql and some ASP? Thanx all!!!

gyra
0
gyra
Asked:
gyra
  • 8
  • 8
1 Solution
 
InteqamCommented:
Query like this will return the following results :

SELECT     JobTypes.JobType + '(' + RTRIM(LTRIM(SubTypes.SubType)) + ')', Options.optionName
FROM         Customers INNER JOIN
                      Projects ON Customers.CustomerID = Projects.CustomerID INNER JOIN
                      Items ON Projects.ProjectID = Items.ProjectID INNER JOIN
                      Options ON Items.OptionID = Options.OptionsID INNER JOIN
                      SubTypes ON Options.subTypeID = SubTypes.SubTypeID INNER JOIN
                      JobTypes ON SubTypes.JobTypeID = JobTypes.JobTypeID
WHERE     (Customers.CustomerID = 1)
ORDER BY JobTypes.JobType + '(' + SubTypes.SubType + ')', SubTypes.SubType, Options.optionName
0
 
InteqamCommented:
                      optionName          
---------------------- --------------------
Roofing   (Garage)     Removal of the roof
Roofing   (Home)       Removal of the roof
Roofing   (Home)       Remove Wood        
0
 
gyraAuthor Commented:
hey integam. This gave me a syntax error.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gyraAuthor Commented:
Syntax error (missing operator) in query expression 'Customers.CustomerID = Projects.CustomerID INNER JOIN
                      Items ON Projects.ProjectID = Items.ProjectID INNER JOIN
                      Options ON Items.OptionID = Options.OptionsID INNER JOIN
                      SubTypes ON Option'
0
 
InteqamCommented:
i made those tables at my machine, so the tables and fields names might not be exactly the same.
0
 
gyraAuthor Commented:
looks like you have everything right. Maybe it's my table and field names?
0
 
gyraAuthor Commented:
I changed the names around but still nothing. If you want to see the database, here's the link:


http://www.gyramedia.com/projects.mdb

Thanx for helping!
0
 
InteqamCommented:
is that an Access database?
0
 
InteqamCommented:
SELECT [jobType]+'('+RTrim(LTrim([SubTypes].[SubType]))+')' AS Expr1, jobOptions.jobOption
FROM (((jobItems INNER JOIN jobOptions ON jobItems.jobOptionID = jobOptions.jobOptionID) INNER JOIN subTypes ON jobOptions.subTypeID = subTypes.subTypeID) INNER JOIN jobTypes ON subTypes.jobTypeID = jobTypes.jobTypeID) INNER JOIN (customers INNER JOIN projects ON customers.CustomerID = projects.customerID) ON jobItems.projectID = projects.ProjectID
WHERE (((customers.CustomerID)=1))
ORDER BY [jobType]+'('+RTrim(LTrim([SubTypes].[SubType]))+')', jobOptions.jobOption, subTypes.subType;
0
 
gyraAuthor Commented:
Awesome! we're real close! i'm actually getting results now. Here's how it displays...

Roofing (Garage)      Removal of the roof
Roofing (Home)      Installation of a New AluminumValleys
Roofing (Home)      Installation of new gutters in general
Roofing (Home)      Installation of New Roof
Roofing (Home)      Removal of the roof
Roofing (Home)      Removal of the woods
Roofing (Home)      Timberline 30 year
Siding (Home)      Capping Windows
Siding (Home)      Installation of New American Dream Siding
Siding (Home)      Installation of New insulation
Siding (Home)      Installation of Windows
Siding (Home)      Removal of old Vinyl Siding
Siding (Home)      Sofotts

How can i get it to display like this using either ASP or the database. Thanx again!!!!!!!!!!!

Roofing (Garage)      
                Removal of the roof

Roofing (Home)
      Installation of a New AluminumValleys
      Installation of new gutters in general
      Installation of New Roof
      Removal of the roof
      Removal of the woods
      Timberline 30 year
Siding (Home)
      Capping Windows
      Installation of New American Dream Siding
      Installation of New insulation
      Installation of Windows
      Removal of old Vinyl Siding
      Sofotts

0
 
InteqamCommented:
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<P>
      <TABLE>
            <%
                  Dim rsDataSet
                  ' here you put the code to get the recordset from database.
                  Dim strCurrentGroup
                  strCurrentGroup = ""
                  
                  While not rsDataSet.EOF
                        if rsDataSet.fields("Expr1") <> strCurrentGroup then
                              strCurrentGroup = rsDataSet.fields("Expr1")
                              %>
                                    <TR>
                                          <TD>
                                                <%=strCurrentGroup%>
                                          </TD>
                                          <TD>
                                                &nbsp;
                                          </TD>
                                    </TR>
                              <%
                        end if
                        %>
                              <TR>
                                    <TD>
                                          &nbsp;
                                    </TD>
                                    <TD>
                                          <%=rsDataSet.fields("jobOption")%>
                                    </TD>
                              </TR>
                        <%
                        .movenext
                  Wend
                  
            %>
      </TABLE>
</P>

</BODY>
</HTML>
0
 
gyraAuthor Commented:
ahhh, i see. let me give it a shot and see if i could finally get this done!!! thanx a lot!
0
 
gyraAuthor Commented:
It worked. But the only thing is, is there any way that i could do something like, based on how many groups are selected (right now there's roof home, roof garage, siding home, siding garage... could be more in the future), we could split the view into two columns. Here's what's going on. I'm putting this thing on a page so that someone can print it out. But since there can be a lot of things, this could push the page to two pages instead of one, get it? Here's the code i'm using for displaying.... and once again, thanx a million for helping me out!!!! ....


'******************** CODE ************************

<table width="95%"  border="0" align="center" cellpadding="2" cellspacing="0">
  <tr valign="top">
    <td>
<%
curJob = ""
for i = 0 to nRows
jobType = work(0,i)
jobOption = work(1,i)
showGroup = false

if jobType <> curJob then
curJob = jobType
showGroup = true
end if
%>
<% if showGroup then %>
<% if i <> 0 then %>
<br>
<% end if %>
<%=jobType%><br>
<% end if %>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<%=jobOption%><br>

        <%
next
%>
</td>
  </tr>
</table>

'**************** END CODE **********************


0
 
InteqamCommented:
this is out of you main question, but can you give more information please, try to draw me a layout of what you are thinking.
0
 
gyraAuthor Commented:
i'll just try to figure it out. thanx for your help!

gyra
0
 
InteqamCommented:
glad i helped
0

Featured Post

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!

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now