Skip to main content
A
Anonymous
May 6, 2008
Question

Query

  • May 6, 2008
  • 2 replies
  • 289 views
1st table member and 2nd table offers
Member can have many offers.
Now I want to have list of all members who have at list one offer.(all members who have at list one offer but one member only one time in list not as many time as offers)
Result I want is like
John 5 offers
Adam 9 offers
Yogesh 1 offer etc..
(Optional: Jimmy no offer
Helan no offer
Kim no offer)
What I have in table is
table member=memb_ID, F_Name
Table offers=Offer_ID, memb_ID, Offer

    This topic has been closed for replies.

    2 replies

    B
    BennyLefave
    Inspiring
    May 6, 2008
    If you just want a listing of the members with at least one offer, this is how I'd do it:

    P
    paross1
    Participating Frequently
    May 6, 2008
    Something resembling these queries?

    --If all you want is members with offers:
    SELECT m.memb_ID,
    m.F_Name,
    COUNT(o.Offer_ID) AS offer_count
    FROM member m
    INNER JOIN offers o ON m.memb_ID = o.memb_ID
    GROUP BY m.memb_ID, m.F_Name

    --If you want all members and counts, even those
    --with none (0)
    SELECT m.memb_ID,
    m.F_Name,
    COUNT(o.Offer_ID) AS offer_count
    FROM member m
    LEFT JOIN offers o ON m.memb_ID = o.memb_ID
    GROUP BY m.memb_ID, m.F_Name

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices