Select with multiples conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select with multiples conditions

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2014-03-21 : 09:20:03

I have two table one Address and the other Territory.
I want to combine both info that is shown in the SELECT result below.

Table Address

Number Street
------ --------
234 Broadway
537 Broadway
538 Broadway

Table Territory

Street From To Odd Even Zone
-------- ---- --- --- ---- --------
Broadway 1 500 0 0 SECTOR A
Broadway 501 600 1 0 SECTOR B
Broadway 501 600 0 1 SECTOR C

When Odd and Even are at 0 on the same line, then the number between 1 and 500 can be both.
When Odd is at 1 and even at 0, the number between 501 and 600 need to be Odd.
When Odd is at 0 and even at 1, the number between 501 and 600 need to be Even.

Here is the SELECT result, i want:

Number Street Zone
------ --------- --------
234 Broadway SECTOR A
537 Broadway SECTOR B
538 Broadway SECTOR C

Any help will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-03-21 : 10:47:24

[code]DECLARE @Address TABLE
(
Number INT NOT NULL,
Street VARCHAR(20) NOT NULL
);

INSERT @Address
(
Number,
Street
)
VALUES (234, 'Broadway'),
(537, 'Broadway'),
(538, 'Broadway');

DECLARE @Territory TABLE
(
Street VARCHAR(20) NOT NULL,
[From] INT NOT NULL,
[To] INT NOT NULL,
Odd BIT NOT NULL,
Even BIT NOT NULL,
Zone VARCHAR(20) NOT NULL
);

INSERT @Territory
(
Street,
[From],
[To],
Odd,
Even,
Zone
)
VALUES ('Broadway', 1, 500, 0, 0, 'SECTOR A'),
('Broadway', 501, 600, 1, 0, 'SECTOR B'),
('Broadway', 501, 600, 0, 1, 'SECTOR C');

-- Solution by SwePeso
SELECT a.Number,
a.Street,
t.Zone
FROM @Address AS a
LEFT JOIN @Territory AS t ON t.Street = a.Street
AND a.Number BETWEEN t.[From] AND t.[To]
AND 1 = CASE
WHEN t.Odd = 1 AND t.Even = 0 AND a.Number % 2 = 1 THEN 1
WHEN t.Odd = 0 AND t.Even = 1 AND a.Number % 2 = 0 THEN 1
WHEN t.Odd = 0 AND t.Even = 0 THEN 1
ELSE 0
END;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

matrixrep
Starting Member

30 Posts

Posted - 2014-03-21 : 11:51:37

Thanks SwePeso . That does the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources