Add column from different table to SQL query (if it finds the id in a list)

Question

I am trying to build a tricky SQL statement and I need some advice.

I have these 2 tables:

         subscribers
| id | name    | email              |
| 1  | John Doe| john.doe@domain.com|
| 2  | Jane Doe| jane.doe@domain.com|
| 3  | Mr Jones| mr.jones@domain.com|

and

         links
| id | campaign_id    | link                                  | id_of_user_that_clicked |
| 1  | 8              | http://somesite.com/?utm_source=news1 | 1,2,3                   |
| 2  | 8              | http://somesite.com/?utm_source=news2 | 1,2                     |
| 3  | 5              | http://somesite.com/?utm_source=news3 | 2                       |

To pull the name and email of Mr. Jones is fast. I run:

SELECT name, email FROM subscribers WHERE id IN ('3')

But I want to add to the result the URL that he clicked stored in the link column of the second table.

I tried to do something similar to:

SELECT name, email FROM subscribers WHERE id IN ('3') LEFT JOIN SELECT link FROM links WHERE (id_of_user_that_clicked LIKE '3')

to no avail

Notice that in the second table, I have the id stored with other id's as well. How can I match subscribers.id with corresponding number in links.id_of_user_that_clicked and have the query display the link next to the name and email.

Any ideas?

Im guessing you need to use some form of `Pivot`, and look at the userID's as separate rows for every link. That is assuming that normalizing your data is not an option, you could have a links table and a UserID-LinkID table between them. — Daniel E., Nov 21 '13 at 15:39

score 1 · Accepted Answer · answered Nov 26 '13 at 11:58

thank you for the help. I managed to build a query to serve my purposes like this. I am pasting the code used:

SELECT s.name, s.email, l.link FROM subscribers s, links l WHERE s.id IN ('.$subscribers.') AND FIND_IN_SET(s.id, l.id_of_user_that_clicked)

$subscribers is a comma separated array (3456, 7865, 267, etc)

It works like a charm.

score 0 · Answer 2 · answered Nov 21 '13 at 15:29

0

You probably want the string to contain 3, not be like 3. Try this:

SELECT name, email, link FROM subscribers LEFT JOIN link on id_of_user_that_clicked LIKE '%3%' WHERE subscribers.id = '3'

answered Nov 21 '13 at 15:29

Snorre

955
1
5
18

agree, but there were only sigle digits in his example :) – Snorre Nov 21 '13 at 18:59

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

Joins are recommended in MySQL as mysql-joins are faster than subqueries, Join vs. sub-query
your sql would be something like (assuming you want to hard-code the name)

 select s.name,s.email, l.campaign_id,l.link from subscribers s left outer join links l on s.id= l.id wher s.name = "Mr Jones" <br>

let me know if it helps!!!

edited May 23 '17 at 12:12

Community

1
1

answered Nov 21 '13 at 15:37

NoobEditor

15,563
19
81
112

Add column from different table to SQL query (if it finds the id in a list)

3 Answers3