SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting related rows with each row

    Hi there

    I'm stuck with this query so I'm really hoping someone could give me a solution.

    Here is my query so far:
    Code:
    SELECT
    	DISTINCT mb.business_id,
    	mb.member_id,
    	mb.tel,
    	mb.addr1,
    	mb.addr2,
    	mb.town,
    	mb.county,
    	mb.postcode,
    	mb.company,
    	mb.url,
    	mb.description,
    	(
    		SELECT COUNT(*)
    		FROM members_business_endo
    		WHERE business_id = mb.business_id
    	) AS endocnt
    FROM
    	members_business mb
    LEFT
    	JOIN members_business_keywords mbk
    	ON mbk.business_id = mb.business_id
    WHERE
    	mbk.keyword LIKE 'fishmonger'
    ORDER BY
    	endocnt DESC, mb.company ASC
    The query works but it doesn't give me all the information I need...

    You will notice the sub select query which returns the number of "endo's" (think of endo's as business employees) for each business.

    What I also need to be able to do is retrieve a list of employee names for each business being selected. So for each business displayed on screen, it needs to show how many employees and also list all those employees.

    If I do another sub select then I obviously get the error that there is more than 1 row returned in the sub select. I could do another query in my loop which displays the results from this query but I definitely don't want that as that would be another 20 queries for each page.

    I'd really appreciate some help here. Many thanks indeed

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    two options:

    1) use GROUP_CONCAT()

    2) join the members_business_endo just like you did with the members_business_keywords table

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Longneck,

    I was just popping back to say that I had found a solution :
    Code:
    SELECT
        DISTINCT mb.business_id,
        mb.member_id,
        mb.tel,
        mb.addr1,
        mb.addr2,
        mb.town,
        mb.county,
        mb.postcode,
        mb.company,
        mb.url,
        mb.description,
        (
            SELECT COUNT(*)
            FROM members_business_endo
            WHERE business_id = mb.business_id
        ) AS endocnt,
        (
            SELECT GROUP_CONCAT(CONCAT(firstname,' ',lastname))
            FROM
                members m,
                members_business_endo me
            WHERE
                me.business_id = mb.business_id
                AND m.member_id = me.endorser_id
        ) AS names
    FROM
        members_business mb
    LEFT JOIN members_business_keywords mbk ON mbk.business_id = mb.business_id
    WHERE
        mbk.keyword LIKE 'design' 
    ORDER BY
        endocnt DESC, mb.company ASC
    So it looks like that corresponds with your first suggestion.
    Is my use of CONCAT suitable is there an easier way to do it?

    Out of interest, wouldn't option 2 have returned an instance of each business for every row returned in the LEFT JOIN?

    Thanks

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sxtrail View Post
    Out of interest, wouldn't option 2 have returned an instance of each business for every row returned in the LEFT JOIN?
    yes. and with most database servers, that would be the only option. in your host language, it would be up to you to format the result. this is a common task, so you should learn how to do it.

    mysql can make some of these situations easier to manage with group_concat() but you must realize that it is not a standard SQL and other database servers do not support it.

    also, you should remove DISTINCT from your query. if you just added it because it made your results look correct, then you probably have a flaw in your data or your query that needs to be addressed. DISTINCT is a very resource intensive option and should not be used lightly.

  5. #5
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could always us

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Longneck,
    Thanks for the info, much appreciated. So removing the GROUP_CONCAT and using a left join would be the answer to this problem: http://www.sitepoint.com/forums/showthread.php?t=541016 ?
    I really need to get this sorted within the next hour and whatever the solution it can't be too resource intensive. Thanks

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    So, the above query could be re written like this?
    Code:
    SELECT
        DISTINCT mb.business_id,
        mb.member_id,
        mb.tel,
        mb.addr1,
        mb.addr2,
        mb.town,
        mb.county,
        mb.postcode,
        mb.company,
        mb.url,
        mb.description,
        (
            SELECT COUNT(*)
            FROM members_business_endo
            WHERE business_id = mb.business_id
        ) AS endocnt
        ,
        m.firstname,
        m.lastname
    FROM
        members_business mb
    LEFT JOIN
        members_business_endo me
        ON me.business_id = mb.business_id
    LEFT JOIN
        members m
        ON m.member_id = me.endo_id
    LEFT JOIN
        members_contacts mc
        ON mc.member_id = 3
        AND mc.contact_member_id = me.endo_id
        OR
        mc.contact_member_id = 3
        AND mc.member_id = me.endo_id
    
    WHERE
        ( mb.town LIKE 'Windsor'
    )
    ORDER BY
        endocnt DESC, mb.company ASC
    Although I need to remove the DISTINCT as you say.

    Thanks

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    Well I thought the above query would work, but it doesn't

    It is imperative that the list of names (m.firstname, m.lastname) are ONLY members that are link to the given member, which in this case is the member with id 3. I am trying to get a list of members names which are linked to the current business (first and second left join) and are also linked to the given member (id 3). However, the query is returning names of members that are linked to the business regardless of whether or not the member is linked to the given member.

    Please help

    Edit: Basically, the 3rd LEFT join of members_contacts does absolutely nothing. The query would work in the same way without it. Unfortunately I see no way of fixing it to get the required result.
    Last edited by sxtrail; Apr 2, 2008 at 16:18.

  9. #9
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Making some progress by using IN and a sub query within a LEFT JOIN. Seems to be working but need to tidy up

  10. #10
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, it looks like this is working:
    Code:
    SELECT
        mb.business_id,
        mb.member_id,
        mb.tel,
        mb.addr1,
        mb.addr2,
        mb.town,
        mb.county,
        mb.postcode,
        mb.company,
        mb.url,
        mb.description,
        (
            SELECT COUNT(*)
            FROM members_business_endo
            WHERE business_id = mb.business_id
        ) AS endocnt,
    	CONCAT(m.firstname,' ',m.lastname) AS name
    FROM
        members_business mb
    LEFT JOIN
        members m
        ON m.member_id IN
        (
            SELECT
                m.member_id
            FROM
                members_contacts mc,
                members_business_endo me
            WHERE
                me.business_id = mb.business_id
                AND m.member_id = me.endo_id
                AND
                (
                    mc.member_id = 3
                    AND mc.contact_member_id = me.endo_id
                    OR
                    mc.contact_member_id = 3
                    AND mc.member_id = me.endo_id
                )
        )
    WHERE
        mb.town LIKE 'Windsor'
    ORDER BY
        endocnt DESC, mb.company ASC
    Does that looks acceptable? I would really appreciate any thoughts or areas it could be improved. Maybe it gives the impression that the data model could be improved?

    Many thanks

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    wow... talk about overkill. try this instead:
    Code:
    SELECT
        mb.business_id,
        mb.member_id,
        mb.tel,
        mb.addr1,
        mb.addr2,
        mb.town,
        mb.county,
        mb.postcode,
        mb.company,
        mb.url,
        mb.description,
        (
            SELECT COUNT(*)
            FROM members_business_endo
            WHERE business_id = mb.business_id
        ) AS endocnt,
        (
            SELECT GROUP_CONCAT(CONCAT(firstname,' ',lastname))
            FROM
                members m,
                members_business_endo me
            WHERE
                me.business_id = mb.business_id
                AND m.member_id = me.endorser_id
        ) AS names
    FROM
        members_business mb
    WHERE
        mb.business_id IN (SELECT mbk.business_id
                             FROM members_business_keywords mbk
                            WHERE mbk.keyword LIKE 'design')
    ORDER BY
        endocnt DESC, mb.company ASC

  12. #12
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Longneck,

    Thanks for the reply.

    I was using GROUP_CONCAT before, but it simply won't work properly in the version of MYSQL I'm using on my server. Plus your query doesn't include the members_contact table, which is essential:
    Code:
    AND
                (
                    mc.member_id = 3
                    AND mc.contact_member_id = me.endo_id
                    OR
                    mc.contact_member_id = 3
                    AND mc.member_id = me.endo_id
                )
    So bearing that in mind, is my query stll overkill?

    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •