Sunday, 8 September 2013

SELECT issue in MySQL

SELECT issue in MySQL

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: given a
private_user_id (a user), list the events that has responded, retrieving
the public_user_id of the creator.
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 ref_proposal_id = proposal_id AND ref_event_id = event_id)
but the public_user_id retrieved is the user that has responded, while I
need the event creator.

No comments:

Post a Comment