Skip to main content
A
Anonymous
May 1, 2008
Question

I want to qury on

  • May 1, 2008
  • 6 replies
  • 533 views
1 I have Member table
2 their offers
Now I want to have list for only those out of hundred members who have offered (entry).
I can try like this
<cfqury name"getonlythosmemberswhoareactive" datasource"#request.datasourece#">
Select memb.*, Offer.*
From memb, Offer
where 0=0
and memb.memb_ID = offer.memb_ID
And
(Here I need help..... memb_ID 5 has 3 offers, memb_Id 9 has 6 offers, memb_ID 11 has (any number) offers and rest members have no offers yet. I want two members in list)
Select Memb_ID 5 to edit
Select Memb_ID 9 to edit
Select Memb_ID 11 to edit
how can I accomplish query for this.
    This topic has been closed for replies.

    6 replies

    N
    Newsgroup_User
    Inspiring
    May 2, 2008
    once again, use GROUP BY clause in your sql:

    Select memb.*, Offer.*
    FROM memb, Offer
    WHERE 0=0
    AND memb.memb_ID = offer.memb_ID
    GROUP BY memb.memb_ID

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    A
    Anonymous
    May 2, 2008
    Yes I joind table and it returns but what happend is
    it gives as many records as their offers are.
    I want only one entry shown for one member and letter on I will link it to their entire record page.
    it should be like under but I dont know how I can accomplish
    where 0=0
    and A_Memb.Memb_ID = O_Offere.Memb_ID
    -----up to here it works fine.
    now?
    <cfif O_Offer record count is > than 2
    Show only one entry
    How can I do this?

    D
    Dan_Bracuk
    Inspiring
    May 1, 2008
    You already have that because you joined the tables in your query.
    A
    Anonymous
    May 1, 2008
    If I say I want list of only those members who have some entry in offer table.

    List of all members
    where
    offer table has at list 1 offer.
    D
    Dan_Bracuk
    Inspiring
    May 1, 2008
    To start, instead of this:
    Select memb.*, Offer.*
    select only the fields you really need.

    For the rest, your question is not clear to me either.
    N
    Newsgroup_User
    Inspiring
    May 1, 2008
    adding GROUP BY memb.memb_id to your query will return only distinct
    members no matter how many offers they have, if that is what you want...
    i am having a bit of a hard time figuring out what exactly you are after...

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices