I'm trying to construct a difficult SQL Server query to select specific data. First, let me give you some background information...
I'm using a database in which I need to reach 3 tables using this query. You can see the diagram in the picture. As you can see, there are users in this database. These users can buy a subscription to use some public bikes around the city. A subscription has a subscriptiontype. This subscriptiontype declares how long the subscription is valid. This can be for a day, a month or a year. A user can have multiple subscriptions.
With my query, I need to select the userId, the name, the address that consists of a street, number, zipcode, city and countrycode, and the description of the subscriptiontype. For every user, I only need the subscription with the longest duration. so you have to filter the query on subscriptionTypeId DESC. but not only this... a user can have multiple subscriptions from the same type, so I only need the most current one. This means I also have to filter on ValidFrom DESC, on the longest during subscriptiontype.
I know I need to use a subquery for this, but can't seem to find a decent query that works... This is how far I got, but I've been using multiple techniques to try to get what I need:
SELECT su.userId, u.name, u.street, u.number, u.zipcode, u.CountryCode, st.description, MAX(su.validFrom) AS MaxValidFrom, (SELECT MAX(SubscriptionTypeId) FROM dbo.Subscriptions WHERE UserId = su.UserId) AS MaxSubscriptionTypeId FROM dbo.Users u INNER JOIN dbo.Subscriptions su ON u.userId = su.userId INNER JOIN dbo.SubscriptionTypes st ON su.subscriptionTypeId = st.subscriptionTypeId HAVING su.SubscriptionTypeId = MAX(su.SubscriptionTypeId) ORDER BY su.UserId, MaxValidFrom DESC;
Thanks in advance for helping me!