0

Previous Thread : Oracle - Get data from past year (Dynamic)

Note: Need to join table to show a better result after the query(From previous thread) have been executed.

This is my userMember

userMember Table

userID   memberID 
0001     0001000
0003     0003000
0006     0006000
0008     0008000
0010     0010000
0013     0013000

my MembeTable

memberID  memberName
0001000      John
0003000      Doe
0006000      Rick
0008000      Jack
0010000      Cruise
0013000      Ronnie

I have some error when joining the table where it keeps on duplicate and I not able to obtain the memberName based on userID (From previous thread)

Expected Result(the result that I wanted)

userID  memberName  count
0001        John      3
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
whalesboy
  • 21
  • 8
  • What have you tried so far? Can you post the query that gave you duplicates? We can work from there. – kc2018 Mar 29 '18 at 03:37
  • @kc2018 SELECT memberName,userID, COUNT(userID) AS count FROM BookingTable b, userMember u, MembeTable e WHERE b.userID = u.userID JOIN u.userID = e.userID WHERE b.Timebooked >= SYSDATE - INTERVAL '1' year HAVING COUNT(userID) >4 GROUP BY userID – whalesboy Mar 29 '18 at 03:51
  • @kc2018 can't run the query as show SQL wrong – whalesboy Mar 29 '18 at 03:52
  • MemberTable (e) does not have userID column. – kc2018 Mar 29 '18 at 03:57

1 Answers1

0
SELECT b.userID, m.memberName, COUNT(b.userID) AS count
FROM BookingTable b
INNER JOIN
userMember u
ON b.userID = u.userID
INNER JOIN
MembeTable m
ON u.memberID = m.memberID
WHERE b.Timebooked BETWEEN SYSDATE - INTERVAL '1' year AND SYSDATE
GROUP BY b.userID, m.memberName
HAVING COUNT(b.userID) > 2;

sqlfiddle

kc2018
  • 1,440
  • 1
  • 8
  • 8
  • Thanks! everything works fine, and sorry for the delayed. I do have a question, hope that you can clear it out for me that is that possible "FROM BookingTable b, userMember u , MembeTable m"? – whalesboy Mar 29 '18 at 06:04
  • @whalesboy explicitly define the type of join (inner, left, right, etc) and the joint key is the standard way. However, you could use `FROM BookingTable b, userMember u, MembeTable m WHERE b.userID = u.userID and b.memberID = m.memberID and b.Timebooked BETWEEN...` – kc2018 Mar 29 '18 at 06:14
  • @whalesboy https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause-mysql - applies to SQL in general. – kc2018 Mar 29 '18 at 06:19