SQL Query 1 have count Function and to get data from another SQL Query 2 where memberid is same-CodePudding

My First Query is

SELECT
    memberid,
    count(*) count
From
    dbo.Transactions
group by
    dbo.Transactions.MemberID
having
    count(memberid) > 1

My query 2 is

SELECT
    transactionlog.id,
    transactionlog.transactionid,
    transactionlog.transactionamount,
    transactionlog.transactiondate,
    transactions.MemberID,
    GymMember.FirstName,
from
    dbo.GymMember
    inner join Transactions on
        GymMember.MemberID = Transactions.MemberId
    inner join TransactionLog on
        Transactions.Id = TransactionLog.TransactionId

Results of Query 2 are givne in below image

Now i want to have data where query 1 member id and query 2 member id are same

CodePudding user response：

I was working on it from more than 24 hours and after try and trial i found the solution but Senior can recommend a more proper way to do this My Solution is

SELECT* FROM( SELECT* FROM (select transactionlog.id, transactionlog.transactionid, transactionlog.transactionamount, transactionlog.transactiondate, transactions.MemberID, GymMember.FirstName, GymMember.CellNumber from dbo.GymMember inner join Transactions on GymMember.MemberID = Transactions.MemberId inner join TransactionLog on Transactions.Id = TransactionLog.TransactionId) as MYTABLE where Exists (select dbo.transactions.memberid,count(dbo.transactions.memberid) From dbo.Transactions Where mytable.MemberID = dbo.Transactions.MemberID group by dbo.Transactions.MemberID having count(dbo.Transactions.MemberID) > 1)) AS mynewtable where convert(datetime,TransactionDate,103) between '2022-09-30 00:00:00' and '2022-10-01 00:00:00'

CodePudding user response：

Do you mean find data where the MemberID from the second query exists in the first query? If so please see query below.

SELECT
    transactionlog.id,
    transactionlog.transactionid,
    transactionlog.transactionamount,
    transactionlog.transactiondate,
    transactions.MemberID,
    GymMember.FirstName,
from
    dbo.GymMember
    inner join Transactions on
        GymMember.MemberID = Transactions.MemberId
    inner join TransactionLog on
        Transactions.Id = TransactionLog.TransactionId
WHERE 
Transactions.MemberId IN 
(SELECT
    memberid
FROM
    dbo.Transactions
group by
    dbo.Transactions.MemberID
having
    count(memberid) > 1))