Simplify SELECT statement - SQL Server Forums

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)

Simplify SELECT statement

Author

Topic

pser
Starting Member

15 Posts

Posted - 2011-01-12 : 03:17:25

Hi,

I have a challenge which I in a way have manage to solve, but I think I have done it to complicated, any of you see ways to simplify it. I have two tables where with a relation ship where table A has primary key and table B has table A as foregn key. Table B can contain n number of record related to one record in table A. In table B there is a column named approve which is of type bit. I have created a query that will list a number of columns from table A and then I want to list a 'yes' or 'No' if one of n record has the field approved in table B set to true. Initially I had a simpler query that worked somehow, but if table B didn't have any record for a registered record in table A I would get NULL. So I wanted to get rid of this NULL and set 'No'.

This is my solution:

SELECT TOP (100) PERCENT dbo.A.DelMID,
dbo.A.oHeading AS Heading,
dbo.A.oStart,
dbo.A.oStop,
CASE WHEN ISNULL(CAST((SELECT TOP (1) CASE WHEN dbo.B.Approved = 1 THEN 1 ELSE 0 END AS Approved
FROM dbo.B
WHERE (dbo.A.DelMID = dbo.B.DelMID)
ORDER BY Approved DESC) as INT),0)=0 THEN 'No' ELSE 'Yes' END AS Approved,
CASE WHEN (A.eAction11 = 1 AND A.eAction11VDate <> '' AND A.eAction12 = 1 AND
A.eAction12VDate <> '' AND A.eAction13 = 1 AND A.eAction13VDate <> '') OR
A.eAction13Ready = 1 THEN 'Yes' ELSE 'No' END AS Completed,
dbo.A.RefNo
FROM dbo.A
INNER JOIN dbo.Person AS Planner ON Planner.PersonID = dbo.A.oPlannerID
ORDER BY dbo.A.oHeading

So can it be simplified or do you have suggestion of how to query and list result in a easier way. I consider myself to think to complicated..

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 03:46:08

Try the query below and also post some details on the indexes you have.
What I think is you should ideally have an index on B.Approved,A.DelMID,B.DelMID.With so many case condition I beleive it is a basic design flaw of the database design.


SELECT dbo.A.DelMID, 
dbo.A.oHeading AS Heading, 
dbo.A.oStart, 
dbo.A.oStop,
CASE WHEN T.Approved=0  THEN 'No' ELSE 'Yes' END AS Approved,
CASE WHEN (A.eAction11 = 1 AND A.eAction11VDate <> '' AND A.eAction12 = 1 AND 
A.eAction12VDate <> '' AND A.eAction13 = 1 AND A.eAction13VDate <> '') OR
A.eAction13Ready = 1 THEN 'Yes' ELSE 'No' END AS Completed, 
FROM dbo.A 
INNER JOIN dbo.Person AS Planner ON Planner.PersonID = dbo.A.oPlannerID
CROSS APPLY(SELECT TOP (1) CASE WHEN dbo.B.Approved = 1 THEN 1 ELSE 0 END AS Approved FROM dbo.B WHERE (dbo.A.DelMID = dbo.B.DelMID)ORDER BY Approved DESC)T
ORDER BY dbo.A.oHeading

PBUH

pser
Starting Member

15 Posts

Posted - 2011-01-12 : 04:14:45

The query does only select data from two tables, so I don't completely agree on the bad database design. However it do get complicated since table B contain information that where every registration related to a record in table A need to be approve before record in table A can be approved. Regarding all the cases I introduced them maybe to lack of knowledge on more complicated queries. I just wanted the query to list.

I have now tested your suggestion and it party working, there is a record that is not listed and this record has no record in table B.

But does it need to be so complicated?

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 04:42:37

Yes I agree that it selects on two tables but the columns in the case condition is the reason why I am saying it a flawed design.

You need to post some sample data and expected o/p for us to understand what exactly you want.

PBUH

pser
Starting Member

15 Posts

Posted - 2011-01-12 : 05:51:21

Understand, I have listed some of the field which are needed for the query here. But Table A and Table B in database are larger.

Table A
RefNo, Heading, start, Stop, Approved
2 Test 01.01.2011 10.01.2011 1
4 Test1 05.01.2011 15.01.2011 0
5 Test2 02.10.2011 08.01.2011 1

Table B
ID, RefNo, Part heading, Part Approved
39 2 Part1 0
40 2 Part2 1
41 5 Part1 1

Query result
RefNo, Heading, start, Stop, NumOf, Approved, Part Approved
2 Test1 01.01.2011 10.01.2011 2 Yes No
4 Test1 05.01.2011 15.01.2011 0 No No
5 Test2 02.10.2011 08.01.2011 1 Yes Yes

Simple exponation, the reason for setup like this is that I have a job (Table A) that can be split into several sub job part (Table B) and everything need to be approved.

Does this make thing more clear?

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 08:07:56

I am not to sure why you want Part Approved=No for refNo 2 and Heading='Test1'.


declare @TableA table (RefNo int, Heading varchar(10), start date, Stop date, Approved int)
insert @TableA
select 2, 'Test', '01.01.2011' ,'10.01.2011' ,1 union
select 4, 'Test1', '05.01.2011', '01.15.2011' ,0 union
select 5, 'Test2', '02.10.2011' ,'08.01.2011', 1

select * from @TableA

declare @TableB table(ID int, RefNo int, Partheading  varchar(10), PartApproved int)
insert @TableB
select 39, 2, 'Part1' ,0 union
select 40, 2, 'Part2' ,1  union
select 41, 5, 'Part1', 1

select * from @TableB




select max(A.RefNo)RefNo,MAX(A.start)start,MAX(A.Stop)Stop,(A.Heading),count(B.RefNo)NumOf,
MAX(A.approved)approved,MAX(B.PartApproved)PartApproved
from @TableA A left join @TableB B on A.RefNo=B.RefNo
group by B.RefNo,A.Heading

PBUH

pser
Starting Member

15 Posts

Posted - 2011-01-12 : 08:24:09

That looks neat, I will test it. The reason why part2 approved can be No is due to a part of the particular main job (in table A) is not completed and therefore it won't be approved. The heading test1 of that job is just some random text I typed. To explain more regarding the purpose I am trying to achieve. Table A will contain main information about a large job. If that job has a scope which meet certain criteria, then that job will be split into smaller pieces which will be stored in Table B. All the registered job in Table B have to be approved before job in Table A can be approved. Heading in Table B will be different in that it will be describe more what that particular piece of job is.

Does it make sense now?

I will check it, thanks again.

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 08:35:19

quote:
The reason why part2 approved can be No is due to a part of the particular main job (in table A) is not completed and therefore it won't be approved.

I think the above part need to be considered in my query.Except that one just check whether the other columns has the expected data and get back on this.

PBUH

pser
Starting Member

15 Posts

Posted - 2011-01-12 : 08:36:39

I have tested the query and it reports that "Operand data type bit is invalid for max operator". I guess it is this: MAX(A.approved)approved,MAX(B.PartApproved)PartApproved that fails. The column approved is of type bit and contains either 1 or 0.

Any suggestion?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-13 : 14:39:53

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible AND not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

Also, please learn why rows are not records, that columns are not fields.

Think about how silly TOP 100 PERCENT is AND how much better this would be if the data element names made any sense. Why are you doing front end display work in the Database? Did you mean "Personnel", the collective set or "Person", single instance in a one-row table? Furthermore, the same data element changes nams. What need is a role prefix on the base data element:
Personnel.person_id = A.planner_person_id

The predicate (A.eaction11v_date <> '') implies you stored a date as a string. Surely not! It should be a DATE and this predicate might be (A.eaction11_v_date IS NOT NULL). Same problem with all the other dates. And they look like a repeated group of date-flag pairs, a major design flaw. Are you writing SQL with assembly language BIT flags, which would be a disaster?

AGAIN, please post DDL; I think this is where you are teh real problesm.

--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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-13 : 15:01:32

quote:
Originally posted by pser

The query does only select data from two tables, so I don't completely agree on the bad database design. However it do get complicated since table B contain information that where every registration related to a record [sic] in table A need to be approve before record [sic] in table A can be approved. Regarding all the cases I introduced them maybe to lack of knowledge on more complicated queries. I just wanted the query to list.

I have now tested your suggestion and it party working, there is a record [sic] that is not listed and this record [sic] has no record [sic] in table B.

But does it need to be so complicated?

You have three, not two, tables in the query: A, B and Person.

Code like "SELECT TOP (100) PERCENT ..CASE WHEN ISNULL(CAST((SELECT TOP (1) CASE.." is a bitch to optimize and even worse to maintain. Updating ISNULL() to COALESCE()isn't going to help

BUT it is the bad design that is screwing you.

You have a hierarchy of tests and subtests; use a nested sets model for this. As each subtest is approved, then its parent is approved until we get to the root node. Your Table A can probably be reduced to a VIEW off of the test hierarchy table.

Do you know how to write a Nested Sets model or do you want more 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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-13 : 15:46:15

Here is a quick example for one test. The test results are 'A' = absent, not done, 'F' = failed, 'P' = passed

CREATE TABLE TestTree
(test_name CHAR(25) NOT NULL PRIMARY KEY,
test_result CHAR(1) DEFAULT 'A'
CHECk (test_result IN ('A', 'F', 'P')),
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));

DELETE FROM TestTree;
GO
INSERT INTO TestTree
VALUES
('Main Test', 'A', 1, 12),
('Beta Test', 'A', 2, 3),
('Gamma Test', 'A', 4, 11),
('G1 Test', 'A', 5, 6),
('G2 Test', 'P', 7, 8),
('G3 Test', 'F', 9, 10);
GO

Give me the name of a test and I return the lowest current status of all its subtests. Use this logic in an UPDATE and change each test until your get to the root of the tree.

SELECT T2.test_name, MIN(T1.test_result) AS parent_test_results
FROM TestTree AS T1, TestTree AS T2,
WHERE T1.lft BETWEEN T2.lft+1 AND T2.rgt-1
AND T2.test_name = @in_test_name
GROUP BY T2.test_name;

--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

pser
Starting Member

15 Posts

Posted - 2011-01-14 : 03:17:27

Hi,

That was a lot of feedback, thank you. I guess I am a newbie trying to get things to work and I must admit I use forums and Google a lot to find my ways around and it was there I found some parts that ended up into the solution I wrote here. But I understood that it was not a good solution, so I thought that this forum might be the right place asking if it is possible to do it easier. I guess there are different quality solution out there and someone does solve problem quick and dirty. For me the best thing is that I learn more every day.

You're also right regarding the number of tables, I forgot the Person table and I must also admit that I have not used a standard for naming. At least the dates are of type datetime ;-)

Unfortunately I don't have to much skills writing "Nested Sets model".

I have 21 tables in my database and have now create the script for creating the three table that we're talking about here. You will see that there are reference to other table in this code, but they are not needed regarding this query. I have also removed some columns in some of the tables as they are not needed for this purpose here and of course it will take less space.

CREATE TABLE [dbo].[DelMelding](
[DelMID] [int] NOT NULL,
[RefNr] [int] NOT NULL,
[oPlanleggerID] [int] NOT NULL,
[oOverskrift] [nvarchar](255) NULL,
[oStart] [datetime] NULL,
[oSlutt] [datetime] NULL,
[eAksjon11] [bit] NULL,
[eAksjon11Dato] [datetime] NULL,
[eAksjon11Signatur] [nvarchar](255) NULL,
[eAksjonV11] [bit] NULL,
[eAksjon11SendtTil] [nvarchar](255) NULL,
[eAksjon11SendtAv] [nvarchar](255) NULL,
[eAksjon11VDato] [datetime] NULL,
[eAksjon12] [bit] NULL,
[eAksjon12Dato] [datetime] NULL,
[eAksjon12Signatur] [nvarchar](255) NULL,
[eAksjonV12] [bit] NULL,
[eAksjon12SendtTil] [nvarchar](255) NULL,
[eAksjon12SendtAv] [nvarchar](255) NULL,
[eAksjon12VDato] [datetime] NULL,
[eAksjon13] [bit] NULL,
[eAksjon13Spenningssetting] [bit] NULL,
[eAksjon13Dato] [datetime] NULL,
[eAksjon13Signatur] [nvarchar](255) NULL,
[eAksjonV13] [bit] NULL,
[eAksjon13SendtTil] [nvarchar](255) NULL,
[eAksjon13SendtAv] [nvarchar](255) NULL,
[eAksjon13VDato] [datetime] NULL,
CONSTRAINT [PK_Delmelding] PRIMARY KEY CLUSTERED
(
[DelMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DelMelding] WITH CHECK ADD CONSTRAINT [FK_DelMelding_FerdigMelding] FOREIGN KEY([RefNr])
REFERENCES [dbo].[FerdigMelding] ([RefNr])
GO

ALTER TABLE [dbo].[DelMelding] CHECK CONSTRAINT [FK_DelMelding_FerdigMelding]
GO

ALTER TABLE [dbo].[DelMelding] WITH CHECK ADD CONSTRAINT [FK_DelMelding_Person] FOREIGN KEY([oPlanleggerID])
REFERENCES [dbo].[Person] ([PersonID])
GO

ALTER TABLE [dbo].[DelMelding] CHECK CONSTRAINT [FK_DelMelding_Person]
GO

CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirmaID] [int] NULL,
[Fornavn] [nvarchar](255) NULL,
[Etternavn] [nvarchar](255) NULL,
[Epost] [nvarchar](255) NULL,
[RolleID] [int] NULL,
[Tlf] [nvarchar](255) NULL,
[Brukernavn] [nvarchar](255) NULL,
[Admin] [bit] NOT NULL,
[Slettet] [bit] NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Firma] FOREIGN KEY([FirmaID])
REFERENCES [dbo].[Firma] ([FirmaID])
GO

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Firma]
GO

ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Roller] FOREIGN KEY([RolleID])
REFERENCES [dbo].[Roller] ([RolleID])
GO

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Roller]
GO

CREATE TABLE [dbo].[DelMTilbakemeldinger](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DelMID] [int] NOT NULL,
[Type] [int] NULL,
[Dato] [datetime] NULL,
[Tilbakemelding] [nvarchar](max) NULL,
[Godkjent] [bit] NULL,
[GodkjentAvID] [int] NOT NULL,
[Lagret] [bit] NOT NULL,
CONSTRAINT [PK_DFeMTilbakemeldinger] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DelMTilbakemeldinger] WITH CHECK ADD CONSTRAINT [FK_DelMTilbakemeldinger_DelMelding] FOREIGN KEY([DelMID])
REFERENCES [dbo].[DelMelding] ([DelMID])
GO

ALTER TABLE [dbo].[DelMTilbakemeldinger] CHECK CONSTRAINT [FK_DelMTilbakemeldinger_DelMelding]
GO

I will try to make some test data as well and post it.

Thanks

pser
Starting Member

15 Posts

Posted - 2011-01-14 : 03:42:33

I have created some test data quick to follow up the create code:

INSERT INTO Person
VALUES
(1,1,'FirstnameA','LastnameA','FA.La@test.com',1,'+12 123 123 123',FirstLastA',0,0),
(2,1,'FirstnameB','LastnameB','FB.La@test.com',1,'+12 124 124 124',FirstLastB',0,0),
(3,2,'FirstnameC','LastnameC','FC.La@toast.com',1,'+12 223 223 223',FirstLastC',1,0);
GO
INSERT INTO DelMelding
VALUES
(1,1,2,'JOb part A',CONVERT(datetime,'2011-01-14 09:25:19'),CONVERT(datetime,'2011-01-24 09:25:19'),
0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00'),
0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00'),
0,0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00')),
(2,1,2,'JOb part A',CONVERT(datetime,'2011-01-14 09:25:19'),CONVERT(datetime,'2011-01-24 09:25:19'),
0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00'),
0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00'),
0,0,CONVERT(datetime,'1900-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'1900-01-01 00:00:00'))
(3,1,2,'JOb part A',CONVERT(datetime,'2011-01-14 09:25:19'),CONVERT(datetime,'2011-01-24 09:25:19'),
0,CONVERT(datetime,'2010-12-01 00:00:00'),'',0,'','',CONVERT(datetime,'2010-12-10 00:00:00'),
0,CONVERT(datetime,'2010-12-06 00:00:00'),'',0,'','',CONVERT(datetime,'2010-12-21 00:00:00'),
0,1,CONVERT(datetime,'2011-01-01 00:00:00'),'',0,'','',CONVERT(datetime,'2011-01-20 00:00:00'));
GO
INSERT INTO DelMTilbakemeldinger
(1,1,1,CONVERT(datetime,'2011-01-06 00:00:00'),'More work need to be done before approval',0,1,1),
(2,1,1,CONVERT(datetime,'2011-01-09 00:00:00'),'You must consider A and B before approval',0,1,1),
(3,3,1,CONVERT(datetime,'2011-01-11 00:00:00'),'Excellent work, approved',1,3,1);

I have started looking into Nested set model and this is a new area. Thanks for the tip

pser
Starting Member

15 Posts

Posted - 2011-01-14 : 03:57:43

I understand the bas databsae design now, I guess I neeed more reading and example.

Thanks for guidance.

pser
Starting Member

15 Posts

Posted - 2011-01-18 : 04:28:08

I have now solved it quite easier than initially. Solution is
SELECT delmelding.delmid, CASE WHEN (SELECT ISNULL(MAX(CAST(delmtilbakemeldinger.godkjent AS INT)),0)
FROM delmtilbakemeldinger WHERE delmtilbakemeldinger.delmid=delmelding.delmid)=1 THEN 'Yes' ELSE 'No' End as Approved FROM DelMelding

I am know more satisfied instead of the initial complicated way of doing it. Now I just have to add the extra fields for completeing the whole query.

Cheers

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-18 : 04:50:51

Greetings Pål! Not very often I see fellow Norwegians here :) This is an excellent place to get help...A LOT of really smart people that will go to great lengths to help you if you abide by the rules (basically be polite and to post working sample data and expected output)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

pser
Starting Member

15 Posts

Posted - 2011-01-18 : 05:42:01

:-) I guess you notice some of the words of the table. Usually I prefer English in design, but in this case it is all Norwegian.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources