SELECT different columns based on the value of another column

microsoft.public.sqlserver.programming

23 Sep 2005 3:26 PM

Chris

Is it possible to select different columns in a query based on the value of
another column. E.g. I have a query which has a column called MyYesNoField
(bit). If MyYesNoField is 0 then I want to return 3 columns from a joined
table. If MyYesNoField is 1 then I want to return 3 columns from a
different table joined in the query. At the moment the following query
returns the 3 columns from the one table AND 3 columns from the other table.

Code to create test environment (and expected results at the bottom)...

CREATE TABLE [dbo].[Test] (
[TestSerialNo] [int] NOT NULL ,
[MyYesNoField] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestTableOne] (
[TestTableOneSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[TestSerialNo] [int] NOT NULL ,
[Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestTableTwo] (
[TestTableTwoSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[TestSerialNo] [int] NOT NULL ,
[Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Test] WITH NOCHECK ADD
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[TestSerialNo]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTableOne] WITH NOCHECK ADD
CONSTRAINT [PK_TestTableOne] PRIMARY KEY CLUSTERED
(
[TestTableOneSerialNo]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTableTwo] WITH NOCHECK ADD
CONSTRAINT [PK_TestTableTwo] PRIMARY KEY CLUSTERED
(
[TestTableTwoSerialNo]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTableOne] ADD
CONSTRAINT [FK_TestTableOne_Test] FOREIGN KEY
(
[TestSerialNo]
) REFERENCES [dbo].[Test] (
[TestSerialNo]
) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TestTableTwo] ADD
CONSTRAINT [FK_TestTableTwo_Test] FOREIGN KEY
(
[TestSerialNo]
) REFERENCES [dbo].[Test] (
[TestSerialNo]
) ON DELETE CASCADE
GO

INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (1, 1)
INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (2, 0)
INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (3, 0)
INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (1,
'a1', 'a2', 'a3')
INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (2,
'b1', 'a2', 'a3')
INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (3,
'c1', 'c2', 'c3')
INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (1,
'd1', 'd2', 'd3')
INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (2,
'e1', 'e2', 'e3')
INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (3,
'f1', 'f2', 'f3')

When I do a select statement I want it to return the 3 columns from
TestTableOne if MyYesNoField = 0 and the 3 columns from TestTableTwo if
MyYesNoField = 1.

E.g.
TestSerialNo    MyYesNoField    Code1    Code2    Code3
=========================================
1                              1                      a1            a2
a3
2                              0                      e1            e2
e3
3                              0                      f1             f2
f3

Thanks,
Chris

23 Sep 2005 3:38 PM

Ian Stocks

"Chris" <cw@community.nospam> wrote in message
news:OZaZkMFwFHA.3312@TK2MSFTNGP09.phx.gbl...
> When I do a select statement I want it to return the 3 columns from
> TestTableOne if MyYesNoField = 0 and the 3 columns from TestTableTwo if
> MyYesNoField = 1.
>
> E.g.
> TestSerialNo    MyYesNoField    Code1    Code2    Code3
> =========================================
> 1                              1                      a1            a2 a3
> 2                              0                      e1            e2 e3
> 3                              0                      f1             f2 f3

select t.*,
case when t.MyYesNoField = 1 then t1.code1 else t2.code1 end as Code1,
case when t.MyYesNoField = 1 then t1.code2 else t2.code2 end as Code2,
case when t.MyYesNoField = 1 then t1.code3 else t2.code3 end as Code3
from test t
left join testtableone t1 on t.TestSerialNo = t1.TestSerialNo
left join TestTabletwo t2 on t.TestSerialNo = t2.TestSerialNo

23 Sep 2005 3:40 PM

Alejandro Mesa

Chris,

Thanks for the ddl and sample data. Try using a "case" expression:

select
   a.TestSerialNo,
   a.MyYesNoField,
   case when a.MyYesNoField = 0 then b.Code1 else c.Code1 end as Code1,
   case when a.MyYesNoField = 0 then b.Code2 else c.Code2 end as Code2,
   case when a.MyYesNoField = 0 then b.Code3 else c.Code3 end as Code3
from
   [dbo].[Test] as a
   inner join
   [dbo].[TestTableOne] as b
   on a.TestSerialNo = b.TestSerialNo
   inner join
   [dbo].[TestTableTwo] as c
   on a.TestSerialNo = c.TestSerialNo

AMB

Show quote

"Chris" wrote:

> Is it possible to select different columns in a query based on the value of
> another column. E.g. I have a query which has a column called MyYesNoField
> (bit). If MyYesNoField is 0 then I want to return 3 columns from a joined
> table. If MyYesNoField is 1 then I want to return 3 columns from a
> different table joined in the query. At the moment the following query
> returns the 3 columns from the one table AND 3 columns from the other table.
>
>
> Code to create test environment (and expected results at the bottom)...
>
> CREATE TABLE [dbo].[Test] (
> [TestSerialNo] [int] NOT NULL ,
> [MyYesNoField] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TestTableOne] (
> [TestTableOneSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [TestSerialNo] [int] NOT NULL ,
> [Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TestTableTwo] (
> [TestTableTwoSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [TestSerialNo] [int] NOT NULL ,
> [Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Test] WITH NOCHECK ADD
> CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
> (
>   [TestSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableOne] WITH NOCHECK ADD
> CONSTRAINT [PK_TestTableOne] PRIMARY KEY CLUSTERED
> (
>   [TestTableOneSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableTwo] WITH NOCHECK ADD
> CONSTRAINT [PK_TestTableTwo] PRIMARY KEY CLUSTERED
> (
>   [TestTableTwoSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableOne] ADD
> CONSTRAINT [FK_TestTableOne_Test] FOREIGN KEY
> (
>   [TestSerialNo]
> ) REFERENCES [dbo].[Test] (
>   [TestSerialNo]
> ) ON DELETE CASCADE
> GO
>
> ALTER TABLE [dbo].[TestTableTwo] ADD
> CONSTRAINT [FK_TestTableTwo_Test] FOREIGN KEY
> (
>   [TestSerialNo]
> ) REFERENCES [dbo].[Test] (
>   [TestSerialNo]
> ) ON DELETE CASCADE
> GO
>
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (1, 1)
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (2, 0)
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (3, 0)
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (1,
> 'a1', 'a2', 'a3')
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (2,
> 'b1', 'a2', 'a3')
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (3,
> 'c1', 'c2', 'c3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (1,
> 'd1', 'd2', 'd3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (2,
> 'e1', 'e2', 'e3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (3,
> 'f1', 'f2', 'f3')
>
>
> When I do a select statement I want it to return the 3 columns from
> TestTableOne if MyYesNoField = 0 and the 3 columns from TestTableTwo if
> MyYesNoField = 1.
>
> E.g.
> TestSerialNo    MyYesNoField    Code1    Code2    Code3
> =========================================
> 1                              1                      a1            a2
> a3
> 2                              0                      e1            e2
> e3
> 3                              0                      f1             f2
> f3
>
>
> Thanks,
> Chris
>
>
>

23 Sep 2005 4:06 PM

Chris

Thanks for the replies.

If I was to have say 10 or so columns instead of the three in my example
would it still be efficient?

Thanks,
Chris

Show quote

"Chris" <cw@community.nospam> wrote in message
news:OZaZkMFwFHA.3312@TK2MSFTNGP09.phx.gbl...
> Is it possible to select different columns in a query based on the value
> of another column. E.g. I have a query which has a column called
> MyYesNoField (bit). If MyYesNoField is 0 then I want to return 3 columns
> from a joined table. If MyYesNoField is 1 then I want to return 3 columns
> from a different table joined in the query. At the moment the following
> query returns the 3 columns from the one table AND 3 columns from the
> other table.
>
>
> Code to create test environment (and expected results at the bottom)...
>
> CREATE TABLE [dbo].[Test] (
> [TestSerialNo] [int] NOT NULL ,
> [MyYesNoField] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TestTableOne] (
> [TestTableOneSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [TestSerialNo] [int] NOT NULL ,
> [Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TestTableTwo] (
> [TestTableTwoSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
> [TestSerialNo] [int] NOT NULL ,
> [Code1] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code2] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Code3] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Test] WITH NOCHECK ADD
> CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
> (
> [TestSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableOne] WITH NOCHECK ADD
> CONSTRAINT [PK_TestTableOne] PRIMARY KEY CLUSTERED
> (
> [TestTableOneSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableTwo] WITH NOCHECK ADD
> CONSTRAINT [PK_TestTableTwo] PRIMARY KEY CLUSTERED
> (
> [TestTableTwoSerialNo]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TestTableOne] ADD
> CONSTRAINT [FK_TestTableOne_Test] FOREIGN KEY
> (
> [TestSerialNo]
> ) REFERENCES [dbo].[Test] (
> [TestSerialNo]
> ) ON DELETE CASCADE
> GO
>
> ALTER TABLE [dbo].[TestTableTwo] ADD
> CONSTRAINT [FK_TestTableTwo_Test] FOREIGN KEY
> (
> [TestSerialNo]
> ) REFERENCES [dbo].[Test] (
> [TestSerialNo]
> ) ON DELETE CASCADE
> GO
>
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (1, 1)
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (2, 0)
> INSERT INTO Test (TestSerialNo, MyYesNoField) VALUES (3, 0)
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (1,
> 'a1', 'a2', 'a3')
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (2,
> 'b1', 'a2', 'a3')
> INSERT INTO TestTableOne (TestSerialNo, Code1, Code2, Code3) VALUES (3,
> 'c1', 'c2', 'c3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (1,
> 'd1', 'd2', 'd3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (2,
> 'e1', 'e2', 'e3')
> INSERT INTO TestTableTwo (TestSerialNo, Code1, Code2, Code3) VALUES (3,
> 'f1', 'f2', 'f3')
>
>
> When I do a select statement I want it to return the 3 columns from
> TestTableOne if MyYesNoField = 0 and the 3 columns from TestTableTwo if
> MyYesNoField = 1.
>
> E.g.
> TestSerialNo    MyYesNoField    Code1    Code2    Code3
> =========================================
> 1                              1                      a1            a2 a3
> 2                              0                      e1            e2 e3
> 3                              0                      f1             f2 f3
>
>
> Thanks,
> Chris
>
>

23 Sep 2005 4:13 PM

Alexander Kuznetsov

this also might be what you need:

select
   a.TestSerialNo,
   a.MyYesNoField,
Code1,
Code2,
Code3
from
   [dbo].[Test] as a
   inner join
   [dbo].[TestTableTwo] as c
   on a.TestSerialNo = c.TestSerialNo
where a.MyYesNoField = 1
union all
select
   a.TestSerialNo,
   a.MyYesNoField,
   Code1,
Code2,
Code3
from
   [dbo].[Test] as a
   inner join
   [dbo].[TestTableOne] as b
   on a.TestSerialNo = b.TestSerialNo
where a.MyYesNoField = 0

23 Sep 2005 4:37 PM

--CELKO--

The design looks awful. IDENTITY instead of real keys, BIT datatypes
to destroy expansion and portability, the magical VARCHAR(50) that
newbies love and tables with identical structures in violation of basic
relational design. It looks like you are attribute splitting.

A table is a set of one kind of thing and it appears once and only once
in the schema, So in order for your query to make sense,these two
tables would be the same kind of things and should be in the same
table. Do you have "MalePersonnel" and "FemalePersonnel" tables in a
schema? Of course not! That is splitting on gender code values and
violates DKNF among other things.

If the two tables model different entities, say squids and automobiles,
then a query that mixes them together is absurd. A query is a table,
too, and hs to be one kind of thing.

Most overly complex DML is the result of bad DDL. Let's try a
relational design instead,

CREATE TABLE ProperTests
(test_nbr INTEGER NOT NULL,
test_type INTEGER NOT NULL
    CHECK (test_type IN (1,2)
PRIMARY KEY (test_nbr,test_type),
code_1 CHAR(2) NOT NULL,
code_2 CHAR(2) NOT NULL,
code_3 CHAR(2) NOT NULL);

Now the query is simply:

SELECT P.*
FROM ProperTests AS P,
       TestReport AS T
WHERE T.test_nbr = P.test_nbr
   AND T.test_type = P.test_type;

Since you did not tell us if a test number can appear in one or both
tables, we would have to use OUTER JOINs in the original schema, or
build the ProperTests as a VIEW, derived table or CTE with a UNION ALL.

23 Sep 2005 5:19 PM

Chris

Celko,

>> The design looks awful

I agree. This was just a very simple test. This represents nothing close
to our actual database design. It was just a quick test so I could get a
quick idea on the actual concept.

>> IDENTITY instead of real keys, BIT datatypes to destroy expansion and
>> portability, the magical VARCHAR(50) that newbies love and tables with
>> identical structures in violation of basic relational design.

I've seen your arguments many times on this newsgroup about IDENTITY fields.
I could not care less about whether you think they are a good idea or not -
they are there as a feature in SQL Server and I and many other people use
them. Regarding the BIT field, we will NEVER change to another database
other than SQL Server in the life cycles of the product we are developing.
Do not come back with this is a narrow minded view - it is not, it's fact.
VARCHAR(50) - This was just an example, nothing more.

>> A table is a set of one kind of thing and it appears once and only once
>> in the schema, So in order for your query to make sense,these two
>> tables would be the same kind of things and should be in the same
>> table. Do you have "MalePersonnel" and "FemalePersonnel" tables in a
>> schema? Of course not! That is splitting on gender code values and
>> violates DKNF among other things.

As I said above, this was just a simple example. I am not going to publish
our database design on this newsgroup, but trust me what we are trying to
achieve is the most efficient approach possible. I am not saying I know
everything about database design (far from it), but for you to criticise me
on what clearly is just a quick example is ridiculous. Surely you could not
think my tables were really called Test, TestTableOne and TestTableTwo?

>> Since you did not tell us if a test number can appear in one or both
>> tables, we would have to use OUTER JOINs in the original schema, or
>> build the ProperTests as a VIEW, derived table or CTE with a UNION ALL.

I was simply after an answer as to whether you can theoretically retrieve
different columns based on some kind of criteria. Other users gave me a
nice simple answer instead of insulting me on a database design which
clearly is not my proper tables.

Chris

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1127493429.190035.306590@z14g2000cwz.googlegroups.com...
> The design looks awful. IDENTITY instead of real keys, BIT datatypes
> to destroy expansion and portability, the magical VARCHAR(50) that
> newbies love and tables with identical structures in violation of basic
> relational design. It looks like you are attribute splitting.
>
> A table is a set of one kind of thing and it appears once and only once
> in the schema, So in order for your query to make sense,these two
> tables would be the same kind of things and should be in the same
> table. Do you have "MalePersonnel" and "FemalePersonnel" tables in a
> schema? Of course not! That is splitting on gender code values and
> violates DKNF among other things.
>
> If the two tables model different entities, say squids and automobiles,
> then a query that mixes them together is absurd. A query is a table,
> too, and hs to be one kind of thing.
>
> Most overly complex DML is the result of bad DDL. Let's try a
> relational design instead,
>
> CREATE TABLE ProperTests
> (test_nbr INTEGER NOT NULL,
> test_type INTEGER NOT NULL
>    CHECK (test_type IN (1,2)
> PRIMARY KEY (test_nbr,test_type),
> code_1 CHAR(2) NOT NULL,
> code_2 CHAR(2) NOT NULL,
> code_3 CHAR(2) NOT NULL);
>
> Now the query is simply:
>
> SELECT P.*
> FROM ProperTests AS P,
>       TestReport AS T
> WHERE T.test_nbr = P.test_nbr
>   AND T.test_type = P.test_type;
>
> Since you did not tell us if a test number can appear in one or both
> tables, we would have to use OUTER JOINs in the original schema, or
> build the ProperTests as a VIEW, derived table or CTE with a UNION ALL.
>

23 Sep 2005 5:28 PM

Change varchar(50) to something more appropriate for the occasion, like
varchar(48), and then go stick your nose in the corner until class lets out.
;-)

Show quote

"Chris" <cw@community.nospam> wrote in message
news:uUfvsLGwFHA.624@TK2MSFTNGP11.phx.gbl...
> Celko,
>
>>> The design looks awful
>
> I agree. This was just a very simple test. This represents nothing close
> to our actual database design. It was just a quick test so I could get a
> quick idea on the actual concept.
>
>>> IDENTITY instead of real keys, BIT datatypes to destroy expansion and
>>> portability, the magical VARCHAR(50) that newbies love and tables with
>>> identical structures in violation of basic relational design.
>
> I've seen your arguments many times on this newsgroup about IDENTITY
> fields. I could not care less about whether you think they are a good idea
> or not - they are there as a feature in SQL Server and I and many other
> people use them. Regarding the BIT field, we will NEVER change to another
> database other than SQL Server in the life cycles of the product we are
> developing. Do not come back with this is a narrow minded view - it is
> not, it's fact. VARCHAR(50) - This was just an example, nothing more.
>
>>> A table is a set of one kind of thing and it appears once and only once
>>> in the schema, So in order for your query to make sense,these two
>>> tables would be the same kind of things and should be in the same
>>> table. Do you have "MalePersonnel" and "FemalePersonnel" tables in a
>>> schema? Of course not! That is splitting on gender code values and
>>> violates DKNF among other things.
>
> As I said above, this was just a simple example. I am not going to
> publish our database design on this newsgroup, but trust me what we are
> trying to achieve is the most efficient approach possible. I am not
> saying I know everything about database design (far from it), but for you
> to criticise me on what clearly is just a quick example is ridiculous.
> Surely you could not think my tables were really called Test, TestTableOne
> and TestTableTwo?
>
>>> Since you did not tell us if a test number can appear in one or both
>>> tables, we would have to use OUTER JOINs in the original schema, or
>>> build the ProperTests as a VIEW, derived table or CTE with a UNION ALL.
>
> I was simply after an answer as to whether you can theoretically retrieve
> different columns based on some kind of criteria. Other users gave me a
> nice simple answer instead of insulting me on a database design which
> clearly is not my proper tables.
>
>
> Chris
>
>
> "--CELKO--" <jcelko***@earthlink.net> wrote in message
> news:1127493429.190035.306590@z14g2000cwz.googlegroups.com...
>> The design looks awful. IDENTITY instead of real keys, BIT datatypes
>> to destroy expansion and portability, the magical VARCHAR(50) that
>> newbies love and tables with identical structures in violation of basic
>> relational design. It looks like you are attribute splitting.
>>
>> A table is a set of one kind of thing and it appears once and only once
>> in the schema, So in order for your query to make sense,these two
>> tables would be the same kind of things and should be in the same
>> table. Do you have "MalePersonnel" and "FemalePersonnel" tables in a
>> schema? Of course not! That is splitting on gender code values and
>> violates DKNF among other things.
>>
>> If the two tables model different entities, say squids and automobiles,
>> then a query that mixes them together is absurd. A query is a table,
>> too, and hs to be one kind of thing.
>>
>> Most overly complex DML is the result of bad DDL. Let's try a
>> relational design instead,
>>
>> CREATE TABLE ProperTests
>> (test_nbr INTEGER NOT NULL,
>> test_type INTEGER NOT NULL
>>    CHECK (test_type IN (1,2)
>> PRIMARY KEY (test_nbr,test_type),
>> code_1 CHAR(2) NOT NULL,
>> code_2 CHAR(2) NOT NULL,
>> code_3 CHAR(2) NOT NULL);
>>
>> Now the query is simply:
>>
>> SELECT P.*
>> FROM ProperTests AS P,
>>       TestReport AS T
>> WHERE T.test_nbr = P.test_nbr
>>   AND T.test_type = P.test_type;
>>
>> Since you did not tell us if a test number can appear in one or both
>> tables, we would have to use OUTER JOINs in the original schema, or
>> build the ProperTests as a VIEW, derived table or CTE with a UNION ALL.
>>
>
>

23 Sep 2005 6:01 PM

--CELKO--

>> I am not going to publish our database design on this newsgroup, but trust me what we are trying to achieve is the most efficient approach possible. <<

A lot of people that post here also say the same thing about using
cursors, dynamic SQL and other bad practices.

When people write out a quick example, they tend to use the same
programming style they use in production code. The facts that your
quick example was so weak, you confused fields and columns, use flags
in SQL and don't know that the behavior of BIT has changed from release
to release of SQL Server says that there is a **very** good chance that
you have some serious design flaws.

This is based on teaching and cleaning up SQL for a decade or two.

23 Sep 2005 6:26 PM

Chris

1. I do say Field when I mean Column sometimes I will admit that. Hardly a
major problem in the case as Field was just part of my name for the column
in my simple example.
2. I did not know BIT has changed from release to release of SQL Server.
I've only ever developed using SQL Server 2000. However, to pull me up on
this is unfair. If a data type is available in SQL Server and it seems to
suit my needs I am going to use it - you really expect me to do hours of
searching on this in the hope I can find a problem with bit fields when I
should not know a problem exists anyway. If Microsoft changed this from
release to release I would expect to see lots of articles in google with a
quick search. I can't. Therefore I assume this is not a major problem
unless you can tell me otherwise.
3. The example was "weak" because I don't have hours to spare thinking on a
stunning database design to post. I will not post our proper database
design. The point was that I did a quick example which other people
understood and was not expecting people to critisise the design when it
clearly is a quick test and has nothing to do with the problem I was aksing
a solution for.

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1127497738.993113.195750@g49g2000cwa.googlegroups.com...
>>> I am not going to publish our database design on this newsgroup, but
>>> trust me what we are trying to achieve is the most efficient approach
>>> possible. <<
>
> A lot of people that post here also say the same thing about using
> cursors, dynamic SQL and other bad practices.
>
> When people write out a quick example, they tend to use the same
> programming style they use in production code. The facts that your
> quick example was so weak, you confused fields and columns, use flags
> in SQL and don't know that the behavior of BIT has changed from release
> to release of SQL Server says that there is a **very** good chance that
> you have some serious design flaws.
>
> This is based on teaching and cleaning up SQL for a decade or two.
>