Query Help

  • I'm writing a query comparing the join_date of our members with the subscriptions.payment_date. Technically they should be the same and I'm trying to find the records where they are not. The problem lies in that I'm getting all the payment dates and not the first payment date that should correspond to the join date. The existing query is pasted below.

    So how do I get it to display the oldest subscriptions.payment date and not all the others?

    Many thanks!

    select name.id,name.join_date,name.paid_thru,subscriptions.payment_date from name,subscriptions

    where member_type like '%m%'

    and name.paid_thru is null

    and name.id = subscriptions.bt_id

    and subscriptions.product_code like 'duesm%'

    or

    member_type like 'm%'

    and name.paid_thru <> subscriptions.payment_date

    and name.id = subscriptions.bt_id

    and subscriptions.product_code like 'duesm%'

    order by name.id,subscriptions.payment_date,name.paid_thru

  • You can use "EXCEPT" do return all rows that exist in Name that do not exist in subscription. Using the following query will return all the Names and Join dates from [Name] where there are no matches in subscriptions.

    SELECT Name.ID, Name.Join_Date

    FROM Name

    EXCEPT

    SELECT subscriptions.bt_id, subscriptions.payment_date

    FROM subscriptions

  • I've been working with the information you supplied, thank you but I'm just not getting what I think I need. I really only need this line:

    and name.paid_thru <> subscriptions.payment_date

    to work llike

    name.paid_thru <> ((select min)(subscriptions.payment_date))

    but that just doesn't seem to be working correclty.

    Does that make sense?

  • Something like this?

    SELECT *

    FROM Name a

    INNER JOIN subscriptions b ON a.NameID = b.bt_id AND a.Join_Date <>

    (SELECT MIN(c.payment_date) FROM subscriptions c WHERE c.bt_id = a.NameId)

  • Another method:

    SELECT a.NameId,

    MIN(a.Join_Date) AS [Join_Date],

    MIN(b.payment_date) AS [Payment_Date]

    FROM Name a

    INNER JOIN subscriptions b ON a.NameID = b.bt_id

    GROUP BY a.NameId

    HAVING MIN(a.Join_Date) <> MIN(b.payment_date)

  • that's got it, thank you for your help.

  • NP. Thanks for the feedback.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply