query to make concordance between two table

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

General SQL Server Forums

New to SQL Server Programming

query to make concordance between two table

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-24 : 17:01:57

here I am trying to create the concordance between the folowing two tables by using this query:

create view [dev].[CI2CD_ccd]
as
SELECT a.[CI] CI
,b.[CD] CD
FROM [dev].[CI] a
,[dev].[CD] b
--,[comm_description] comm_description
where substring (a.CI,2,2) <= b.CD <= substring (a.CI,5,2)

But it gives error.

where: CI is
$01-05
$06-09
$10-14
$15-19

and CD is:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
17
18

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-24 : 17:10:45

What error are yout receiving?

try this:

SELECT a.[CI] CI
,b.[CD] CD 
FROM [dev].[CI] a 
,[dev].[CD] b
--,[comm_description] comm_description
where substring (a.CI,2,2) <= b.CD 
AND b.CD <= substring (a.CI,5,2)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-24 : 21:17:14

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-25 : 14:20:01

Thank you Lamprey, it works. Thank you jcelko for introducing the sources.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources