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

x
?
Solved

SQL Table Join issue

Posted on 2008-06-19
11
Medium Priority
?
267 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:Brendt Hess
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 32

Expert Comment

by:Brendt Hess
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
 
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:Brendt Hess
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 600 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

773 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