CODEWITHSUNDEEP
mysql
user1647347
user1647347

Reputation: 507

mysql getting data and looking it up in another table

I've got two tables in my database. Table 1 is a list of "timelines" and their corresponding owners and title.

Table 2 is a list of users who have access to the timelines but are followers, not owners.

I'm trying to write a query that outputs the lineID's and corresponding titles that are linked to a userID in either of the two tables.

A query for userID 1 would ideally output:

1 a
2 b
3 c
6 f

Hopefully this isn't too confusing but the purpose is to fill a dynamically generated select box with the LineID and Title for a given UserID...

Table 1 ("owners")
--------------------------
| LineID | UserID | Title |
| 1      | 1      | a     |
| 2      | 1      | b     |
| 3      | 1      | c     |
| 4      | 2      | d     |
| 5      | 2      | e     |
| 6      | 1      | f     |
--------------------------


Table 2 ("followers")
----------------------------
| RowID | LineID | UserID   |
| 1     | 1      | 1        |
| 2     | 1      | 2        |
| 3     | 3      | 1        |
| 4     | 3      | 2        |
| 5     | 2      | 2        |
| 6     | 6      | 1        |
----------------------------

I tried using:

SELECT title 
FROM `lines` 
LEFT JOIN follow 
    ON follow.user_id = lines.user_id 
WHERE follow.user_id = 1

That ended up producing duplicate rows.

The output I need would ideally be an array consisting of all the lineID's and Titles associated with that userID.

Upvotes: 2

Views: 103

Answers (1)

Dipul Patel
Dipul Patel

Reputation: 187

select   LineId, Title 
from     owners 
where    LineId in (select LineId from followers group by LineId ) 
order by owners.LineId

Upvotes: 2

Related Questions