Proper way to return value

Question

I have a situation where I want to delete a member from Members table if and only of the member has no transaction in MEMBERS_TRANSACTION table.

I also want to know whether the member has any transaction. Currently I will return a column with the value of 1 to indicate at least one transaction exists for that member, or 0 to indicate that the member has no transaction and has been deleted upon the execution of this query. My query is as follows:

IF EXISTS 
( 
    SELECT members_trans_id 
    FROM MEMBERS_TRANSACTION 
    WHERE member_link_id = 188
) 
    SELECT 1 
ELSE 
    DELETE FROM Members WHERE member_link_id = 188;
    SELECT 0

My concern is the two lines in ELSE block. I can execute this query with no problem at SQL Server Management Studio, but I wonder if it is correct to write it that way.

I'm using SQL server 2008 R2.

Thank you.

SteveCav · Answer 1 · 2014-06-24T04:26:03.343

0

Left Joins are your friend and very fast. To view all members and their transactions:

select m.Name, count(*) As TotalTransactions
from Member m
LEFT JOIN MEMBERS_TRANSACTION mt on mt.member_link_id=m.member_link_id
where mt.member_link_id is null
GROUP BY m.Name
ORDER BY m.Name

(to find only the ones with no transactions, add a HAVING COUNT(*)=0 to the end)

To zap the freeloaders:

delete m
from Member m
LEFT JOIN MEMBERS_TRANSACTION mt on mt.member_link_id=m.member_link_id
where mt.member_link_id is null

I'm assuming you want to do this for all members. Obviously for a single one add their ID in the where clauses.

edited Jun 24 '14 at 04:26

answered Jun 24 '14 at 04:20

SteveCav

6,649
1
50
52

I'm trying to do this in just one query, i.e one database call. As for the second query you gave me, it will delete the selected member (by `member_link_id`) even when the member has transaction in `MEMBERS_TRANSACTION` table – ixora Jun 24 '14 at 06:37

score 0 · Answer 2 · edited May 23 '17 at 11:49

Eigher you join or give sub-query, from both you will achieve this. Good to use join.

In sqlserver 2008, Merge statement introduce which is very good in this condition.

Also read the good article about basic when we use what:-

left outer v/s not exist and not in v/s not exist

Always use select query before delete this query

create table Members (member_link_id int , name varchar(50))
create table MEMBERS_TRANSACTION ( members_trans_id int , member_link_id int, name varchar(50))

insert into Members values(188,'abc'),(189,'abc1'),(190,'abc2'),(192,'abc3'),(193,'abc4')
insert into MEMBERS_TRANSACTION values(1,189,'abc'),(2,190,'abc1')

/*for select with left outer join to understand better*/
select * from Members m left outer join MEMBERS_TRANSACTION mt on m.member_link_id = mt.member_link_id

-/*1st approach*/
select * from Members m 
where not exists ( select members_trans_id from MEMBERS_TRANSACTION mt where m.member_link_id = mt.member_link_id )
and member_link_id = 188

select * from Members m 
where not exists ( select members_trans_id from MEMBERS_TRANSACTION mt where m.member_link_id = mt.member_link_id )
and member_link_id = 189

/*2nd approach with merge option, it is give error*/
MERGE Members AS m
USING (SELECT member_link_id FROM MEMBERS_TRANSACTION ) AS mt
ON mt.member_link_id = m.member_link_id
WHEN MATCHED THEN 
SELECT * from Members 
WHEN NOT MATCHED THEN
SELECT * from Members where m.member_link_id = 188

drop table member
drop table MEMBERS_TRANSACTION

I'm sorry but there's syntax error in the second query. On the other hand, the first query deletes all members whose `member_link_id` is not 188. This is not what I'm looking for. I'm looking for a way to delete member whose `member_link_id` is 188 if and only if this member has no transaction. I also want to know whether the outcome of this query execution, whether the member has been deleted, or he has not been deleted due to existing transaction — ixora, Jun 24 '14 at 06:44

score 0 · Answer 3 · answered Jun 24 '14 at 09:53

Just attempt the delete and examine the modified row count returned by SQL Server:

DELETE FROM Members
WHERE member_link_id = 188 AND NOT EXISTS (
    SELECT *
    FROM MEMBERS_TRANSACTION 
    WHERE member_link_id = 188
)

I recommend to have as little procedural logic in T-SQL. C# is much better equipped to handle logic.

Proper way to return value

3 Answers3