Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 3 tables with the exact same DDL (I know that is a horrid set-up), but each one has a different values for a field. I am hoping to be able to have a way to write ONE query for all 3 selects as opposed to having to use 3 different select queries. Here is sample DDL and the individual queries, could this be merged into 1 query?

Create Table InfoForJane
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)
Create Table InfoForJoe
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)
Create Table InfoForJessie
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)

And here are the queries that I would ideally like merged into one

Select 
genid
,name
,case
  when numofdays NOT IN ('1-3','4-6','7-9') then '1-3'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJane

Select
genid
,name
,case
  when numofdays NOT IN ('1-5','6-10','11-14') then '1-5'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJoe

Select 
genid
,name
,case
  when numofdays NOT IN ('1-2','3-8','8-12') then '1-2'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJessie
share|improve this question
up vote 7 down vote accepted

I think you are looking for the "UNION ALL" operator. See the example below. If you are going to be using this a lot, you could even turn it into a view and then query the view. I've added a column "SourceTable" but that is my personal preference when doing things like this, your needs may vary and it may be superfluous for your environment.

SELECT  genid
      , name
      , SourceTable = 'InfoForJane'
      , CASE WHEN numofdays NOT IN ( '1-3', '4-6', '7-9' ) THEN '1-3'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJane
UNION ALL
SELECT  genid
      , name
      , SourceTable = 'InfoForJoe'
      , CASE WHEN numofdays NOT IN ( '1-5', '6-10', '11-14' ) THEN '1-5'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJoe
UNION ALL
SELECT  genid
      , name
      , SourceTable = 'InfoForJessie'
      , CASE WHEN numofdays NOT IN ( '1-2', '3-8', '8-12' ) THEN '1-2'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJessie;
share|improve this answer

UNION ALL should do it. Like this:

Select 
    genid
    ,name
    ,case
      when numofdays NOT IN ('1-3','4-6','7-9') then '1-3'
      else numofdays
    end As [NumofDays]
    from rashapa.munich.dbo.InfoForJane

    UNION ALL

    Select
    genid
    ,name
    ,case
      when numofdays NOT IN ('1-5','6-10','11-14') then '1-5'
      else numofdays
    end As [NumofDays]
    from rashapa.munich.dbo.InfoForJoe

    UNION ALL

    Select 
    genid
    ,name
    ,case
      when numofdays NOT IN ('1-2','3-8','8-12') then '1-2'
      else numofdays
    end As [NumofDays]
    from rashapa.munich.dbo.InfoForJessie

Or build a view:

CREATE VIEW vwMergedQueries AS

    Select 
        genid
        ,name
        ,case
          when numofdays NOT IN ('1-3','4-6','7-9') then '1-3'
          else numofdays
        end As [NumofDays]
        from rashapa.munich.dbo.InfoForJane

        UNION ALL

        Select
        genid
        ,name
        ,case
          when numofdays NOT IN ('1-5','6-10','11-14') then '1-5'
          else numofdays
        end As [NumofDays]
        from rashapa.munich.dbo.InfoForJoe

        UNION ALL

        Select 
        genid
        ,name
        ,case
          when numofdays NOT IN ('1-2','3-8','8-12') then '1-2'
          else numofdays
        end As [NumofDays]
        from rashapa.munich.dbo.InfoForJessie

Then you can query the view as a single entity:

SELECT * FROM vwMergedQueries
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.