I am fairly new to MYSQl. I am trying to create a query that will fit my needs. I have developed an sqlfiddle.
Here is the story. I have 5 tables. Users, Places, Meetings, Attend, and Met.
In the end, I would like to be able to choose places to view what meetings are held there. I would like to view the attendees for each meeting. I believe I can accomplish all that pretty easily. but...
Here is where I am hung up. I want to show a list of ALL users who are attending a meeting (actually, this should exclude my own id). So one column should be user_names that list all users attending the meeting (excluding me) and another that lists who I have met shown as True or False or something similar. So, All users from attend with specific meeting_id and True for all those from table met who have my user_id and their user_ids as met_user.
I can achieve many different elements of mysql. I am struggling with this one.
Here is my schema
CREATE TABLE users (user_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30) NOT NULL);
CREATE TABLE places (place_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
place_name VARCHAR(30) NOT NULL);
CREATE TABLE meetings (meeting_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
place_id INT(6),
meeting_name VARCHAR(30) NOT NULL);
CREATE TABLE attend (attend_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
meeting_id INT(6),
user_id INT(6));
CREATE TABLE met (met_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
meeting_id INT(6),
user_id INT(6),
met_user INT(6));
INSERT INTO users (user_name)
VALUES ('Jason'), ('Billy'), ('Sarah'), ('Julie'), ('Jennifer'), ('Laura') ;
INSERT INTO places (place_name)
VALUES ('London'), ('Paris'), ('New York'), ('Rome');
INSERT INTO meetings (place_id, meeting_name)
VALUES ('1', 'London Meeting #1'), ('1','London Meeting #2'), ('2','Paris Meeting'), ('3','New York Meeting'), ('4','Rome Meeting');
INSERT INTO attend (meeting_id, user_id)
VALUES ('1', '1'), ('2','1'), ('1','2'), ('1','3'), ('1','4'), ('1','5'), ('1','6');
INSERT INTO met (meeting_id, user_id, met_user)
VALUES ('1', '1', '2'), ('1', '1', '3'),('1', '1', '4'), ('1', '2', '1'), ('1', '2', '3'), ('1', '2', '4');
Here is one of the queries that I think might be closest.
Select u.*, at.meeting_id, at.user_id, m.meeting_name, met.*
from users as u
JOIN attend as at
ON at.user_id = u.user_id
JOIN meetings as m
ON at.meeting_id = m.meeting_id
LEFT JOIN met as met
ON met.user_id = at.user_id
Where m.meeting_id = 1
This query is showing All of my (I am Jason in this example) met_users, all of billy's met_users, and null for all other attendees (which is part of what I want).
**[Results][2]**:
| user_id | user_name | meeting_id | user_id | meeting_name | met_id | meeting_id | user_id | met_user |
|---------|-----------|------------|---------|-------------------|--------|------------|---------|----------|
| 1 | Jason | 1 | 1 | London Meeting #1 | 1 | 1 | 1 | 2 |
| 1 | Jason | 1 | 1 | London Meeting #1 | 2 | 1 | 1 | 3 |
| 1 | Jason | 1 | 1 | London Meeting #1 | 3 | 1 | 1 | 4 |
| 2 | Billy | 1 | 2 | London Meeting #1 | 4 | 1 | 2 | 1 |
| 2 | Billy | 1 | 2 | London Meeting #1 | 5 | 1 | 2 | 3 |
| 2 | Billy | 1 | 2 | London Meeting #1 | 6 | 1 | 2 | 4 |
| 3 | Sarah | 1 | 3 | London Meeting #1 | (null) | (null) | (null) | (null) |
| 4 | Julie | 1 | 4 | London Meeting #1 | (null) | (null) | (null) | (null) |
| 5 | Jennifer | 1 | 5 | London Meeting #1 | (null) | (null) | (null) | (null) |
| 6 | Laura | 1 | 6 | London Meeting #1 | (null) | (null) | (null) | (null) |
Did you want to achieve something like this perhaps? (If not, could you post the query result you are expecting):
select u.user_id,u.user_name,
case when exists (select * from met where met.user_id = 1 AND met.met_user = u.user_id) then 'true' else 'false' end didIMeet
from users as u join attend as at
on u.user_id = at.user_id
join meetings as m
on at.meeting_id = at.meeting_id
where m.meeting_id = 1
and u.user_id <> 1