CODEWITHSUNDEEP
sqlsql-server-2008
dopplesoldner
dopplesoldner

Reputation: 9469

SQL select multiple columns to multiple variables using where

I have a simple table as follows

CREATE TABLE [accounting].[ExtractControl](
    [SourceSchema] [varchar](50) NOT NULL,
    [SourceTable] [varchar](150) NOT NULL,
    [SourceDatabase] [varchar](50) NOT NULL)

I would like to select SourceDatabase using SourceSchema and SourceTable values

Currently I am doing with multiple queries as follows

Select @s1 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'xxx' and SourceTable = 'xxx'
Select @s2 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'yyy' and SourceTable = 'yyy'
Select @s3 = SourceDatabase from accounting.ExtractControl where SourceSchema = 'xxx' and SourceTable = 'yyy'

I do believe that this can be done in more elegant ways! Thanks for the help!

Upvotes: 1

Views: 899

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

option with one query

SELECT @s1 = MAX(CASE WHEN SourceSchema = 'xxx' and SourceTable = 'xxx' THEN SourceDatabase END),
       @s2 = MAX(CASE WHEN SourceSchema = 'yyy' and SourceTable = 'yyy' THEN SourceDatabase END),
       @s3 = MAX(CASE WHEN SourceSchema = 'xxx' and SourceTable = 'yyy' THEN SourceDatabase END)
FROM accounting.ExtractControl

Demo on SQL Fiddle

Upvotes: 1

Pankaj Dubey
Pankaj Dubey

Reputation: 824

what about this Query...

select SourceDatabase
from accounting.ExtractControl
where SourceSchema in ('xxx', 'yyy')
      and SourceTable in ('xxx', 'yyy')
      and SourceTable = SourceSchema

i did not tried this but i think this is what you required

Upvotes: 0

Mert
Mert

Reputation: 6572

select * from accounting.ExtractControl
where SourceSchema = 'xxx' or SourceSchema = 'yyy'

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174289

Are you asking for something like this?

select SourceDatabase from accounting.ExtractControl
where SourceSchema in ('xxx', 'yyy') and SourceTable in ('xxx', 'yyy');

This will return the SourceDatabase where the SourceSchema is either 'xxx' or 'yyy' and the SourceTable is either 'xxx' or 'yyy'.

Upvotes: 1

Related Questions