Item Tracking via Multiple Tables
I'm not sure if I'm explaining this correctly, so I apologize if it's hard to follow.
Basically, I have several tables connected view a single table that contains entries that matching all the other tables. I can pull the information I need via LEFT JOIN in most cases, but I have an issue with one situation. Basically, I need to list the contents of one table and whether or not the entries are tied to the connector table. Here's a simplified example of what I mean:
CREATE TABLE IF NOT EXISTS fruit (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO fruit (name) VALUES ('apples'), ('oranges'), ('peaches'), ('pears');
----------
| fruit |
-- -------
|id| name |
-- -------
| 1| apples|
| 2|oranges|
| 3|peaches|
| 4| pears|
-- -------
CREATE TABLE IF NOT EXISTS connector (
id int(11) NOT NULL AUTO_INCREMENT,
tid int(11) NOT NULL,
uid int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO connector (tid, uid) VALUES (1, 1), (2, 1), (1, 2), (3, 3), (1, 4), (2, 4), (4, 4);
----------
| connector|
-- --- ---
|id|tid|uid|
-- --- ---
| 1| 1 | 1 |
| 2| 2 | 1 |
| 3| 1 | 2 |
| 4| 3 | 3 |
| 5| 1 | 4 |
| 6| 2 | 4 |
| 7| 4 | 4 |
-- --- ---
SELECT a.tid, b.name FROM connector a LEFT JOIN fruit b ON a.tid = b.id WHERE a.uid = 4;
--- -------
|tid| name |
--- -------
| 1 | apples|
| 2 |oranges|
| 4 | pears|
--- -------
I need do this:
---------------
| UID 4 Has |
-- ------- ----
|id| name |Yes?|
-- ------- ----
| 1| apples| 1 |
| 2|oranges| 1 |
| 3|peaches| 0 |
| 4| pears| 1 |
-- ------- ----
I thought I could pull it off with a subquery and a CASE WHEN, but I couldn't figure out how-to make it work. Any suggestions on how I can do this?