troubleshooting Question

Help writing a couple of SQL queries across multiple tables

Avatar of tobzzz
tobzzzFlag for Spain asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
13 Comments1 Solution244 ViewsLast Modified:
Hi Experts,

I need help writing some SQL. I've never been trained and am self-taught, with this one I'm at my limit/out of my depth and need help.

There are 3 tables of data and they relate badly: I did not design the database but I have to work on it :-S

The tables look like this:

activity table
-----------------------------------------
activityid      |      userid      |      addressid      |
-----------------------------------------
      1            |            5      |            8            |
      2            |            19      |            26            |
      3            |            5      |            8            |
      4            |            24      |            31            |
      5            |            5      |            8            |
      6            |            19      |            26            |
-----------------------------------------      

address table
-----------------------------
addressid      |      town            |
-----------------------------
      8            |      Brighton      |
      26            |      Maidstone      |
      31            |      Halstead      |
-----------------------------

payment table
-----------------------------------------------------------------
paymentid      |      payment_town      |      pay_start      |       pay_end            |
-----------------------------------------------------------------
      43            |      Brighton            |      16/03/2011      |      15/03/2012      |
      44            |      Maidstone            |      11/01/2011      |      10/01/2012      |
      45            |      Halstead            |      23/12/2010      |      22/12/2011      |
-----------------------------------------------------------------

How They Relate:

SELECT *
FROM activity AS act
      JOIN address AS addr ON act.addressid = addr.addressid
      JOIN payment AS pay ON addr.town = pay.payment_town
      
The SQL I need is:

1. How many activities (count) each user (userid) has that is assigned to ONE address (addressid) that's payment is currently in date (BETWEEN pay_start AND pay_end) BUT only where there is MORE than 1 activity!

E.g. in my sample tables above, you can see that the userid 5 appears 3 times with the same addressid. So my result should be:
-------------------------------------------------------------------------
userid      |      addressid      |      pay_start      |      pay_end            |      occurances      |
-------------------------------------------------------------------------
      5      |            8            |      16/03/2011      |      15/03/2012      |            3            |
-------------------------------------------------------------------------
The Maidstone address appears twice however it's out of date so would not be found...

2. I need to then have a full list of: act.activityid, act.userid, act.addressid, addr.town, pay.pay_start, pay.pay_end for the above criteria.

So the result would look like this:
-----------------------------------------------------------------------------------------
activityid      |      userid      |      addressid      |      town            |      pay_start      |      pay_end            |
-----------------------------------------------------------------------------------------
      1            |            5      |            8            |      Maidstone      |      16/03/2011      |      15/03/2012      |
      3            |            5      |            8            |      Maidstone      |      16/03/2011      |      15/03/2012      |
      5            |            5      |            8            |      Maidstone      |      16/03/2011      |      15/03/2012      |
-----------------------------------------------------------------------------------------

My main issue is I'm not up-to-scratch with how GROUP BY or HAVING clauses work when there's more than one table. Please help!

Thanks a million

/ Tobzzz
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros