mySQL Query JOIN in same table

Question

Table structure goes something like this:

Table: Purchasers Columns: id | organization | city | state

Table: Events Columns: id | purchaser_id

My query:

SELECT purchasers.*, events.id AS event_id 
FROM purchasers 
INNER JOIN events ON events.purchaser_id = purchasers.id 
WHERE purchasers.id = '$id'

What I would like to do, is obviously to select entries by their id from the purchasers table and join from events. That's the easy part. I can also easily to another query to get other purchasers with the same organization, city and state (there are multiples) but I'd like to do it all in the same query. Is there a way I can do this?

In short, grab purchasers by their ID but then also select other purchasers that have the same organization, city and state.

Thanks.

Answer 1

You could try something like

SELECT  p.*, 
         e.id
FROM    purchasers p  INNER JOIN 
        events e ON e.purchaser_id = p.id  INNER JOIN
        (
            SELECT  p.*
            FROM    purchasers p
            WHERE   p.id = '$id'
        ) Original  ON  p.organization = Original.organization
                    AND p.city = Original.city
                    AND p.state = Original.state

The subselect Original will return the original purchaser, and then link to the purchasers table by organization, city and state

EDIT:

Changed the query, this will still return duplicates, but only for the number of events registered per purchaser. If you wish to retrieve a DISTINCT list of purchasers, you cannot do this with the event id, so you need something like

SELECT  p.*
FROM    purchasers p  INNER JOIN 
        (
            SELECT  p.*
            FROM    purchasers p
            WHERE   p.id = '$id'
        ) Original  ON  p.organization = Original.organization
                    AND p.city = Original.city
                    AND p.state = Original.state