Reputation: 47
I am relatively new to SQL and I will try to get the terminology correct. I have 2 tables, tbl_Trans which holds the general transaction details, and tbl_TransData which holds the details of the transaction.
I want to show each Trans record and the category of the trans which is held in the tbl_TransData. If there is more than one category for each Trans I want the text to be 'Mulitple', otherwise to return the category field
TRANS table content
int_Trans_ID dtm_TransDate txt_Type txt_Description txt_Bank dbl_Amount
1 17/12/2018 REC Sales Current 1000
2 20/12/2018 PAY Expenses paid Current -155
3 21/12/2018 PAY MW Repairs Current -250
TRANSDATA table content
int_TransData_ID int_TransID txt_Category dbl_Amount
1 1 Sales A -600
2 1 Sales B -400
3 2 Travel 100
4 2 Meal 55
5 3 MW Repairs 250
This is the code so far, but if I replace the ELSE 'Single' with ELSE txt_Category it does not work.
SELECT
int_Trans_ID,
dtm_TransDate AS Date,
txt_Type AS Type,
txt_Description AS Description,
(SELECT
CASE
WHEN count(int_TransID) > 1
THEN 'Multiple'
ELSE 'Single'
END
FROM
dbo.tbl_TransData TD
WHERE
TD.int_TransID = T.int_Trans_ID) AS Category
FROM
tbl_Trans T
GROUP BY
int_Trans_ID, dtm_TransDate, txt_Type, txt_Description
This is what I would like to see.
int_Trans_ID Date Type Description Category
1 2018-12-17 REC Sales Multiple
2 2018-12-20 PAY Expenses paid Multiple
3 2018-12-21 PAY Car Repairs MW Repairs
Sorry for the format of the tables.
Any help would be much appreciated, or even pointing me in the direction of other posts.
Upvotes: 3
Views: 38
Reputation: 30663
Please have a look at this, I wrote subquery for case condition
SELECT
int_Trans_ID,
dtm_TransDate,
txt_type,
txt_Description,
CASE WHEN (SELECT COUNT(*) FROM tbl_Trans_Data td WHERE td.int_Trans_ID = t.int_Trans_ID)) > 1 THEN
'MULTİPLE'
ELSE
(SELECT Category from tbl_trans_data td Where td.int_Trans_ID = t.int_Trans_ID)
END AS Category
FROM
tbl_Trans t
Upvotes: 1
Reputation: 48875
I would do:
with
x as (
select ins_trans_id, count(*) as cnt, max(txt_category) as cat
from transdata
group by ins_trans_id
)
select
t.int_trans_id,
t.dtm_transdate,
t.txt_type,
t.txt_description,
case when x.cnt = 0 then 'no category'
when x.cnt = 1 then x.cat
else 'Multiple' end as Category
from trans t
left join x on x.int_trans_id = t.int_trans_id
Upvotes: 2