SELECT issue in MySQL

Question

I am trying to perform a SELECT but I am not able to retrieve the data I want.

I have tried lots of sentences without success, so that's why I am exposing here the whole problem.

Here is my database (four tables):

-- Table users
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(15),
    private_user_id VARCHAR(16),
    public_user_id VARCHAR(16)
);

-- Table events
CREATE TABLE events (
    event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    public_event_id VARCHAR(8),
    ref_user_id INT NOT NULL REFERENCES users(user_id),
    name VARCHAR(64),
    description VARCHAR(256)
);

-- Table proposals
CREATE TABLE proposals (
    proposal_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    public_proposal_id VARCHAR(8),
    ref_event_id INT NOT NULL REFERENCES events(event_id),
    proposal_date VARCHAR(32),
    proposal_location VARCHAR(64),
    proposal_type INT
);

-- Table responses
CREATE TABLE responses (
    response_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ref_user_id INT NOT NULL REFERENCES users(user_id),
    ref_proposal_id INT NOT NULL REFERENCES proposals(proposal_id),
    user_name VARCHAR(32),
    response INT
);

There are users. There are events. Events are created by one user. Events have proposals. Proposals have responses from the users. I need:

I need to achieve this given a private user id. Is something like: given the private user id, analyze the responses of that user, so analyze the proposals of that responses, and finally get the events of that responses, RETURNING THE PUBLIC USER ID of the creator of each event.

I have tried with:

SELECT DISTINCT events.ref_user_id, public_event_id, name, description, public_user_id
FROM events, users, proposals, responses
WHERE responses.ref_user_id = user_id
  AND private_user_id = 'lk0X3lOQl972U7pB'

but the public_user_id retrieved is the user that has responded, while I need the event creator.

Answer 1

SELECT ALL                                    -- show all
    u.pulic_user_id AS creator_pulic_user_id, -- creators' public IDs
    e.name          AS event_name             -- and events' names
FROM                                          -- from
    events AS e                               -- events
  JOIN                                        -- and
    users AS u                                -- their creator 
      ON e.ref_user_id = u.user_id
WHERE                                         -- where
    EXISTS                                    -- exists
    ( SELECT 1                                -- a
      FROM responses AS r                     -- response
        JOIN users AS ur                      -- by a user,
          ON ur.user_id = r.ref_user_id
        JOIN proposals AS p                   -- for a proposal
          ON p.proposal_id = r.ref_proposal_id 
      WHERE p.ref_event_id = e.event_id       -- that is about that event
        AND ur.private_user_id = @X           -- and the responding user  
    ) ;                                       -- has private ID = @X

---

Sidenote: Inline foreign key constraints, like this:

ref_user_id INT NOT NULL REFERENCES users(user_id),

are parsed (only for syntax correctness) and ignored in MySQL. You have to add them in the end of a CREATE TABLE statement to be valid and enforced. Or use ALTER TABLE, if you have already inserted data in the tables.

Answer 2

If it's just the ids that are wrong it's because you're returning public_user_id from the user table, you want events.ref_user_id, try this:

SELECT DISTINCT events.ref_user_id AS public_user_id , public_event_id, name, description
FROM events, users, proposals, responses 
WHERE (responses.ref_user_id = user_id 
  AND ref_proposal_id = proposal_id AND ref_event_id = event_id)

Edit

I think this is what you want:

SELECT DISTINCT e.ref_user_id AS public_user_id 
FROM events e, users u, proposals p, responses r
WHERE r.ref_user_id = u.user_id 
  AND r.ref_proposal_id = p.proposal_id 
  AND p.ref_event_id = e.event_id
  AND u.private_user_id = 'lk0X3lOQl972U7pB'
;