Database

Ho to avoid Not In & In clause in my query

11-Mar-14 2:25

Yes, there is (was) even migration tool to help you upsize to a proper database (SQL Server). You then only need to change your connection string to point to the SQL Server database.

Never underestimate the power of human stupidity
RAH

member002611-Mar-14 0:43

member0026

11-Mar-14 0:43

select id,name from stud where (id not in (select id from class)) and depid in (select depid from dep where rid=@rid)

Bernhard Hiller11-Mar-14 1:04

Bernhard Hiller

11-Mar-14 1:04

What about:

SQL

Select DISTINCT id, name
FROM stud
INNER JOIN dep on stud.depid=dep.depid
LEFT JOIN class on stud.id=class.id
WHERE dep.rid=@rid 
AND stud.id is null

But I doubt that it's faster...

member002611-Mar-14 18:08

member0026

11-Mar-14 18:08

Im not sure, but compare not in the join will be the faster one, i heared like that Frown | :(

thatraja11-Mar-14 2:07

thatraja

11-Mar-14 2:07

JOIN/EXISTS
SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best[^]

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

Adding Sales Totals from multiple tables

GuyThiebaut11-Mar-14 3:54

GuyThiebaut

11-Mar-14 3:54

SQL

select id,name
from stud s
where not exists
(
    select c.id
    from class c
    where c.id = s.id
)
and depid in
(
    select depid
    from dep
    where rid=@rid
)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

jkirkerx9-Mar-14 10:54

jkirkerx

9-Mar-14 10:54

I'm trying to write better TSQL to reduce the amount of functions I have to do things.

Here I'm trying to add the total amount of sales from a column called GrandTotal from multiple tables
into a single result. First I'm not sure if this is even possible to do, but I think it can be done.

I've come this far, but I get 2 results returned. I'm just trying to consolidate them into one.

I tried SELECT SUM(TotalAmount) as the wrapper, but I get a syntax error
I tried UNION ALL without the )( around around it, but I get an error on the last )

The tables are identical in columns

DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);

SELECT
(
    SELECT SUM(GrandTotal)
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
)
    UNION ALL
(
    SELECT SUM(GrandTotal)
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
)

Mycroft Holmes9-Mar-14 14:16

9-Mar-14 14:16

Try - note that I named the sum fields.

SQL

Select sum(GTotal) Grand
From(
SELECT
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
)
    UNION ALL
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
))

Personally I would normally include the common ID and use a Group By and then an inner join to where you need the Grand Totals

Never underestimate the power of human stupidity
RAH

jkirkerx9-Mar-14 17:44

jkirkerx

9-Mar-14 17:44

Thanks!

I understand the example, but I still get the ")" error in which I don't understand. There's only 3 of them.

The common ID and the GroupBy with the inner join is going to take me some time to experiment with.
I'm not really sure where to start with that one.

Jörgen Andersson9-Mar-14 22:01

Jörgen Andersson

9-Mar-14 22:01

Try to turn it into a CTE and see if that works:

SQL

WITH sums AS (
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
    UNION ALL
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
    )
SELECT  Sum(GrandTotal) AS GrandTotal
FROM    sums

Or use a subquery which is the same thing but different syntax.

Wrong is evil and must be defeated.
- Jeff Ello[^]

jkirkerx10-Mar-14 6:26

jkirkerx

10-Mar-14 6:26

Common Table Expression

Yet another way to build more complex queries, Interesting

I had no idea I could express it like that, and would of never thought of looking for it in search.

I was close on the first couple of tries.

Thanks Jorgen!

I did read up on those articles you gave me, and have been designing my new stuff in such a manner.
Writing TSQL for reports can get more complex to produce the desired results.

How to prevent or allow a insert trigger ?

Jörgen Andersson10-Mar-14 8:35

Jörgen Andersson

10-Mar-14 8:35

Glad to be able to help. Smile | :)

Appreciative feed back is the best driving force there is!

Wrong is evil and must be defeated.
- Jeff Ello[^]

ngoloi9-Mar-14 6:15

ngoloi

9-Mar-14 6:15

I have a issue when I create a trigger for insert or update
Create trigger In_Name on CheckStatus for insert, update
as
declare @id int

Select @id=id from inserted where id=@id
if exists (Select * from CheckStatus where id=@id)
begin
rollback transaction
update CheckStatus Set levels=levels+1 where id=@id
end
else
insert into CheckStatus (id,levels) values (@id,1)

But the trigger do not execute my opinion (I want to update "levels" when id field exists, inversely inserting id and levels)
Please, giving a hand, thanks a lot

Mycroft Holmes9-Mar-14 13:09

9-Mar-14 13:09

If your database design needs you to update primary keys (ID Fields) you have bigger problems that trigger design. A primary field should never be modified, with the possible exception of deploying data across different systems.

Never underestimate the power of human stupidity
RAH

ngoloi9-Mar-14 18:02

ngoloi

9-Mar-14 18:02

thanks for answering me. My idea want to update or insert "Levels" fields, @id do not primary keys, it is a foreign keys form other table. I'm a new database studying, please help me.

GuyThiebaut9-Mar-14 22:37

GuyThiebaut

9-Mar-14 22:37

Select @id=id from inserted where id=@id

There's your issue - @id is going to be null as you have only just declared it in the line above.
The trigger does fire it just does not do anything as @id is always going to be null.

Get rid of the

where id=@id

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

ngoloi10-Mar-14 4:28

ngoloi

10-Mar-14 4:28

ok, I show all code again after editing

CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1
for insert,update
as
declare @NgayDG smalldatetime
declare @MaLoaiTT int
Select @MaLoaiTT=MaLoaiTT,@NgayDG=NgayDG from inserted
if exists (Select count(*) from KhaoSat_LoaiDG1 where KhaoSat_LoaiDG1.MaLoaiTT=@MaLoaiTT and KhaoSat_LoaiDG1.NgayDG=@NgayDG)
begin
rollback transaction
update KhaoSat_LoaiDG1 set Muc1=Muc1+1 where MaLoaiTT=@MaLoaiTT and NgayDG=@NgayDG

end
else
insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG)

Update is ok but not insert.I do not know why, please help me.

Sql Server Express: Testing sql-commands

GuyThiebaut10-Mar-14 4:52

GuyThiebaut

10-Mar-14 4:52

It looks like you are performing an insert within your trigger on the table the trigger is running from

CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1

insert into KhaoSat_LoaiDG1

Any clues as to why this may be a bad idea?
Hint - infinity...

Just remove:

SQL

else
insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG)

and see what happens.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 10-Mar-14 12:17pm.

Frygreen9-Mar-14 3:03

Frygreen

9-Mar-14 3:03

Dear reader,

I am totally new in database-programming. I have available all the standard-books but still, I feel lost in that area. I am working with Sql Server Express-Edition

One of my basic questions is: How can I test SQL-commands from any kind of tool. It seems, that the express-edition does not have all the tools installed.

Another question is: Is it ok to start programming with "basic Ado.Net" and not focussing on EF or Linq?

Best regards

modified 10-Mar-14 13:39pm.

Richard MacCutchan9-Mar-14 3:33

Richard MacCutchan

9-Mar-14 3:33

There are many articles here on CodeProject and elsewhere that will give you a good introduction:
SQL for Developers: Basic Data Retrieval[^], http://arcanecode.com/2007/01/25/create-a-sql-server-compact-edition-database-with-c/[^], and http://www.w3schools.com/sql/[^].

Jörgen Andersson9-Mar-14 7:42

Jörgen Andersson

9-Mar-14 7:42

Frygreen wrote:
Another question is: Is it ok to start programming with "basic Ado.Net" and not focussing on EF or Linq?

Oh yes!

Wrong is evil and must be defeated.
- Jeff Ello[^]

Mycroft Holmes9-Mar-14 13:05

9-Mar-14 13:05

I will back Jorgen on this one, do NOT use EF as your data access layer if you intend to become a serious developer who builds data centric solutions. Linq to SQL is basically a disaster wrapped in obscure syntax, learn TSQL and you will reap the benefits your entire career.

Never underestimate the power of human stupidity
RAH

Richard Deeming10-Mar-14 2:49

Richard Deeming

10-Mar-14 2:49

Frygreen wrote:
It seems, that the express-edition does not have all the tools installed.

There are different installation options for SQL Express - the larger "Express with advanced services" or "Express with tools" downloads include Management Studio, whereas the smaller "Express" download doesn't.

If you've downloaded the version without the tools, there's a separate "Management Studio"-only download available on the same page:
http://www.microsoft.com/en-gb/download/details.aspx?id=29062[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer