Solved

SQL Table Join issue

Posted on 2008-06-19
11
209 Views
Last Modified: 2010-03-19
I need the query below modified to make this output, instead of the one listed.
TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)
--------------------------------------------------------------------
NAME     START    END     PAUSE
001        20           50         70
002        25           55         75
SELECT tblNAME.NAME, tblTYPE.TYPE, tblTYPE.VALUE

from tblNAME INNER JOIN

  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID
 

outputs:
 

NAME    TYPE    VALUE

001     1       20

002     1       25

003     2       50

004     2       55

005     3       70 

006     3       75

Open in new window

0
Comment
Question by:kak39
  • 4
  • 4
  • 3
11 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 21825030
How do you associate the start record with the End record, and either with a Pause record?

Will there always be a Pause record?
0
 

Author Comment

by:kak39
ID: 21825112
Sorry little confusing. The working query and output are in the code snipit. I need the TYPE field translated into (START, STOP, PAUSE) based on this info.

TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 21825175
You'll need to use a PIVOT table for this.
SELECT n.[NAME], [1] AS [START], [2] AS [STOP], [3] AS [PAUSE]

  FROM tblNAME n

    INNER JOIN tblTYPE t ON n.NAME_ID = n.NAME_ID

PIVOT (SUM(t.[VALUE]) FOR t.[TYPE] IN ([1], [2], [3])) AS PivotTable

Open in new window

0
 
LVL 32

Expert Comment

by:bhess1
ID: 21825207
The translation is easy.  A simple CASE statement such as:

SELECT
    tblNAME.NAME,
    CASE tblTYPE.TYPE
        WHEN 1 THEN 'START'
        WHEN 2 THEN 'END'
        WHEN 3 THEN 'PAUSE'
    END AS [Type]
    tblTYPE.VALUE
from tblNAME INNER JOIN
  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID
 
But your data sample does not indicate how to get from there to the display output.
0
 

Author Comment

by:kak39
ID: 21825534
bhess, think you got it backwards. The info in the "CODE SNIPPET" section is what i'm currently running SQL and what i'm getting as an output. I need the output in the question

NAME     START    END     PAUSE
001        20           50         70
002        25           55         75
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 11

Expert Comment

by:aaronakin
ID: 21825545
kak39, did you try the PIVOT script I provided?
0
 

Author Comment

by:kak39
ID: 21825662
yes, it had an error with the IND2USER field?
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 21825724
I'm not sure what field you are referring to.  Did you add it?  Can you try running the query I gave you as-is to see if it works?

Can you provide an actual sample of the data from tblNAME and tblTYPE, along with the column names so I can write you an accurate query if the other one I gave you doesn't work?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 21825875
Let's start back at the beginning.

Can you give a data sample that provides all of the data you need to generate your sample output?  That is, the records for NAME 001 and NAME 002.  The data sample you have does not indicate any way to identify which records should be associated with NAME 001, since only one record contains that value.  I can make an assumption (that your sample data should have only NAME 001 and NAME 002 records all the way down), but that may not be a correct assumption.
0
 

Author Comment

by:kak39
ID: 21830588

Two tables:
 

tblNAME

-----------

NAME_ID   NAME

1         001

2         002

3         003

4         004

5         005
 

tblTYPE

--------------------

NAME_ID  TYPE  VALUE

1        1     20

1        2     50

1        3     70

2        1     25

2        2     55

2        3     75
 

The query below works, and this is the output I get. 
 

SELECT tblNAME.NAME, tblTYPE.TYPE, tblTYPE.VALUE

from tblNAME INNER JOIN

  tblTYPE ON tblNAME.NAME_ID = tblTYPE.NAME_ID

 

outputs:

 

NAME    TYPE    VALUE

001     1       20

001     2       50

001     3       70

002     1       25

002     2       55 

002     3       75
 

---------------------------

WHAT I NEED:
 

TYPE = 1 (START) TYPE  =2 (STOP) TYPE  =3 (PAUSE)

--------------------------------------------------------------------

NAME     START    END     PAUSE

001        20      50       70 

002        25      55       75

Open in new window

0
 
LVL 11

Accepted Solution

by:
aaronakin earned 150 total points
ID: 21831462
Try this and see if it gives you what you need.  It works for me.
SELECT [NAME], [1] AS [START], [2] AS [STOP], [3] AS [PAUSE]

  FROM 

      (

      SELECT n.NAME_ID, n.[NAME], t.[TYPE], t.[VALUE]

        FROM tblNAME n

          INNER JOIN tblTYPE t ON n.NAME_ID = t.NAME_ID

      ) a

  PIVOT (SUM([VALUE]) FOR [TYPE] IN ([1], [2], [3])) AS PivotTable

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

19 Experts available now in Live!

Get 1:1 Help Now