Solved

How to combine two select querys

Posted on 2014-03-28
5
186 Views
Last Modified: 2014-03-30
Hi!

Have this query 1:

SELECT
    TRTABLE.TransactionID,
    TRTABLE.Question,
    TRTABLE.UserID,
    TRTABLE.Organisation,
	TRTABLE.CreatedDate,
	TRTABLE.CLID,
	TRTABLE.ControlTransID,
	TRTABLE.LQID,
    TRTABLE.CategoryID_1,
	TRTABLE.Category_1,
	TRTABLE.Note,
	TRTABLE.ControlName,
	CONTR.ControlID,
	LOCOBJQUEST.LocationID,
	TRTABLE.Location,
	TRTABLE.CategoryID_2,
    TRTABLE.Category_2,
	TRTABLE.Category_1_Note,
	TRTABLE.Category_2_Note,
	TRTABLE.Answer,
	LOCOBJQUEST.ObjectID,
	QESTTABLE.IsBool,
	QESTTABLE.IsScale,
	QESTTABLE.Mandatory_QR,
	QESTTABLE.Mandatory_Image,
	QESTTABLE.Question_Type,
	QESTTABLE.ImageYN,
	QESTTABLE.NoteYN,
	USERT.Name,
	OBJT.Object,
	(select distinct(Organisationname) from dbo.SHOW_UserControls where dbo.SHOW_UserControls.Organisation=TRTABLE.Organisation) as 'Organisationname'
	
FROM
    dbo.TransactionTable TRTABLE 
INNER JOIN LocationObjectQuestion LOCOBJQUEST
ON TRTABLE.LQID = LOCOBJQUEST.LOQID
INNER JOIN QuestionsTable QESTTABLE
ON LOCOBJQUEST.QuestionsID = QESTTABLE.QuestionsID
INNER JOIN dbo.ControlTrans CONTR
ON CONTR.ControlTransID=TRTABLE.ControlTransID
INNER JOIN SHOW_User USERT
ON TRTABLE.UserID=USERT.UserID
INNER JOIN dbo.ObjectTable OBJT
ON LOCOBJQUEST.ObjectID=OBJT.ObjectID
AND (TRTABLE.Organisation=OBJT.Organisation)

Open in new window


This query gives me 47 records

But need to add 2 more colums(fields) to this query

Query2 :

SELECT
    "Felt1" =
    Case 
        when dbo.CategoryTable.Category = '%Bil eller Container nr%' then dbo.CategoryTable.Category 
        else dbo.SHOW_Category_2.Category_2
    END,     
    "Felt2" =
    Case 
        when dbo.CategoryTable.Category = 'Bil eller Container nr' then dbo.SHOW_Category_2.Category_2 
        else dbo.CategoryTable.Category
    END        
FROM
    dbo.TransactionTable
RIGHT OUTER JOIN
    dbo.CategoryTable
ON
    (
        dbo.TransactionTable.Category_1 = dbo.CategoryTable.CategoryID)
INNER JOIN
    dbo.SHOW_Category_2
ON
    (
        dbo.TransactionTable.Category_2 = dbo.SHOW_Category_2.CategoryID)

Open in new window


How can i combine this two querys ?
0
Comment
Question by:team2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 39961592
E.g.

WITH FirstQuery AS ( <yourFirstSql>),
      SecondQuery AS ( <yourSecondQuery> )
  SELECT *
  FROM  FirstQuery FQ
    [INNER|LEFT] JOIN SecondQuery SQ ON FQ.<primaryKeyColumns> = SQ.<primaryKeyColumns>;

Open in new window


You need to include the primary key columns from dbo.TransactionTable in both queries.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39962579
This should do the trick, using a CTE to join the columns together.
WITH qry1 AS
(
SELECT
    TRTABLE.TransactionID,
    TRTABLE.Question,
    TRTABLE.UserID,
    TRTABLE.Organisation,
	TRTABLE.CreatedDate,
	TRTABLE.CLID,
	TRTABLE.ControlTransID,
	TRTABLE.LQID,
    TRTABLE.CategoryID_1,
	TRTABLE.Category_1,
	TRTABLE.Note,
	TRTABLE.ControlName,
	CONTR.ControlID,
	LOCOBJQUEST.LocationID,
	TRTABLE.Location,
	TRTABLE.CategoryID_2,
    TRTABLE.Category_2,
	TRTABLE.Category_1_Note,
	TRTABLE.Category_2_Note,
	TRTABLE.Answer,
	LOCOBJQUEST.ObjectID,
	QESTTABLE.IsBool,
	QESTTABLE.IsScale,
	QESTTABLE.Mandatory_QR,
	QESTTABLE.Mandatory_Image,
	QESTTABLE.Question_Type,
	QESTTABLE.ImageYN,
	QESTTABLE.NoteYN,
	USERT.Name,
	OBJT.Object,
	(select distinct(Organisationname) from dbo.SHOW_UserControls where dbo.SHOW_UserControls.Organisation=TRTABLE.Organisation) as 'Organisationname'
	
FROM
    dbo.TransactionTable TRTABLE 
INNER JOIN LocationObjectQuestion LOCOBJQUEST
ON TRTABLE.LQID = LOCOBJQUEST.LOQID
INNER JOIN QuestionsTable QESTTABLE
ON LOCOBJQUEST.QuestionsID = QESTTABLE.QuestionsID
INNER JOIN dbo.ControlTrans CONTR
ON CONTR.ControlTransID=TRTABLE.ControlTransID
INNER JOIN SHOW_User USERT
ON TRTABLE.UserID=USERT.UserID
INNER JOIN dbo.ObjectTable OBJT
ON LOCOBJQUEST.ObjectID=OBJT.ObjectID
AND (TRTABLE.Organisation=OBJT.Organisation)
),
qry2 AS
(
SELECT
    "Felt1" =
    Case 
        when dbo.CategoryTable.Category = '%Bil eller Container nr%' then dbo.CategoryTable.Category 
        else dbo.SHOW_Category_2.Category_2
    END,     
    "Felt2" =
    Case 
        when dbo.CategoryTable.Category = 'Bil eller Container nr' then dbo.SHOW_Category_2.Category_2 
        else dbo.CategoryTable.Category
    END        
FROM
    dbo.TransactionTable
RIGHT OUTER JOIN
    dbo.CategoryTable
ON
    (
        dbo.TransactionTable.Category_1 = dbo.CategoryTable.CategoryID)
INNER JOIN
    dbo.SHOW_Category_2
ON
    (
        dbo.TransactionTable.Category_2 = dbo.SHOW_Category_2.CategoryID)
)

SELECT qry1.*, qry2.*
FROM qry1
INNER JOIN qry2
  ON qry1.id = qry2.id -- this is the primary key that joins the queries together

Open in new window

0
 
LVL 2

Author Comment

by:team2005
ID: 39964851
Hi!

Tryed to use:

ON qry1.TransactionID = qry2.TransactionID

But gives me error:
Invalid column name 'TransactionID'.

??
0
 
LVL 9

Accepted Solution

by:
edtechdba earned 500 total points
ID: 39964882
The TransactionID needs to be included in the SELECT statement for qry2, see below how I added it.
WITH qry1 AS
(
SELECT
    TRTABLE.TransactionID,
    TRTABLE.Question,
    TRTABLE.UserID,
    TRTABLE.Organisation,
	TRTABLE.CreatedDate,
	TRTABLE.CLID,
	TRTABLE.ControlTransID,
	TRTABLE.LQID,
    TRTABLE.CategoryID_1,
	TRTABLE.Category_1,
	TRTABLE.Note,
	TRTABLE.ControlName,
	CONTR.ControlID,
	LOCOBJQUEST.LocationID,
	TRTABLE.Location,
	TRTABLE.CategoryID_2,
    TRTABLE.Category_2,
	TRTABLE.Category_1_Note,
	TRTABLE.Category_2_Note,
	TRTABLE.Answer,
	LOCOBJQUEST.ObjectID,
	QESTTABLE.IsBool,
	QESTTABLE.IsScale,
	QESTTABLE.Mandatory_QR,
	QESTTABLE.Mandatory_Image,
	QESTTABLE.Question_Type,
	QESTTABLE.ImageYN,
	QESTTABLE.NoteYN,
	USERT.Name,
	OBJT.Object,
	(select distinct(Organisationname) from dbo.SHOW_UserControls where dbo.SHOW_UserControls.Organisation=TRTABLE.Organisation) as 'Organisationname'
	
FROM
    dbo.TransactionTable TRTABLE 
INNER JOIN LocationObjectQuestion LOCOBJQUEST
ON TRTABLE.LQID = LOCOBJQUEST.LOQID
INNER JOIN QuestionsTable QESTTABLE
ON LOCOBJQUEST.QuestionsID = QESTTABLE.QuestionsID
INNER JOIN dbo.ControlTrans CONTR
ON CONTR.ControlTransID=TRTABLE.ControlTransID
INNER JOIN SHOW_User USERT
ON TRTABLE.UserID=USERT.UserID
INNER JOIN dbo.ObjectTable OBJT
ON LOCOBJQUEST.ObjectID=OBJT.ObjectID
AND (TRTABLE.Organisation=OBJT.Organisation)
),
qry2 AS
(
SELECT
    TransactionID,
    "Felt1" =
    Case 
        when dbo.CategoryTable.Category = '%Bil eller Container nr%' then dbo.CategoryTable.Category 
        else dbo.SHOW_Category_2.Category_2
    END,     
    "Felt2" =
    Case 
        when dbo.CategoryTable.Category = 'Bil eller Container nr' then dbo.SHOW_Category_2.Category_2 
        else dbo.CategoryTable.Category
    END        
FROM
    dbo.TransactionTable
RIGHT OUTER JOIN
    dbo.CategoryTable
ON
    (
        dbo.TransactionTable.Category_1 = dbo.CategoryTable.CategoryID)
INNER JOIN
    dbo.SHOW_Category_2
ON
    (
        dbo.TransactionTable.Category_2 = dbo.SHOW_Category_2.CategoryID)
)

SELECT qry1.*, qry2.*
FROM qry1
INNER JOIN qry2
  ON qry1.id = qry2.id -- this is the primary key that joins the queries together

Open in new window

0
 
LVL 2

Author Closing Comment

by:team2005
ID: 39964892
thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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