Mysql add column from different table

Question

I have two tables:

| USER |
| _id  | - int id PK
| id   | - String id
| name |

| SUBSCRIBE |
|   user    |
| subscribe |

and I want to extract this data:

| _id | id | name | subscribe

Simple example

| USER |
|  1   | 10212551 | Mike
|  2   | 21022145 | Nick

| SUBSCRIBE |
|    1      |     2


get_info($user, $sub );

| EXPECTED RESULT |
|     $user       |  id  |  name  |  0 or 1 (false or true, if $sub has subscribed to $user)

My best try was with count():

SELECT u._id,u.id,u.name,u.email,u.country,count(s.subscribe) AS subscribe 
FROM user u,subscribe s 
WHERE u._id='$user' AND s.subscribe='$user' AND s.user='$sub'

But this works only if there's any record in subscribe table. So for

get_info(2,1)

it will give right result but for:

get_info(1,2)

there's nothing :/

Can somebody please help me with this ?

M Khalid Junaid · Accepted Answer · 2014-08-10 21:04:03Z

1

Use left join in order to return user even if he has not subscribed and use case to check for subscribed

select u._id,u.id,u.name,u.email,u.country,
case when s.subscribe is not null then 1 else 0 end as subscribe 
from user u 
left join subscribe s on (u._id = s.user and s.subscribe ='$sub')
where u._id='$user'

`Demo`

edited Aug 10, 2014 at 21:04

answered Aug 10, 2014 at 20:45

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

M Khalid Junaid Over a year ago

@MichalHeneš yup there was a typo missing end for case statement try it now

Michal Heneš Over a year ago

That code gives me always subscribe 0 even if there's a record

M Khalid Junaid Over a year ago

@MichalHeneš see my updated answer with sample demo

Michal Heneš Over a year ago

Yay, yes, your code works, but I must retype u._id = s.user and s.subscribe ='$sub' with u._id = s.subscribe and s.user ='$sub' thanks, my bad for not explained it properly :) ty

Collectives™ on Stack Overflow

Mysql add column from different table

1 Answer 1

`Demo`

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related