CODEWITHSUNDEEP
mysql
Ante Medic
Ante Medic

Reputation: 83

MySQL, create view from columns from same table

I have this table:

CREATE TABLE one (
  id bigint(11) primary key,
  email varchar(100),
  refer_link varchar(8),
  referrer varchar (8)
);

When users submit forms they get a unique refer link (refer_link). When another user submit forms with that link in his referrer column is inserted that refer link.

So in example I will have this table:

id      email                   refer_link       referrer
---------------------------------------------------------
1       [email protected]         ref11111 
2       [email protected]       ref22222         ref11111
3       [email protected]       ref33333         ref22222
4       [email protected]       ref44444         ref11111 
5       [email protected]         ref55555         ref44444

How to create this view?

email                   refer_email         refer_count
--------------------------------------------------------
[email protected]                                2 
[email protected]       [email protected]        1
[email protected]       [email protected]      0
[email protected]       [email protected]        1 
[email protected]         [email protected]      0

Thank you very much for help!

Upvotes: 0

Views: 49

Answers (2)

Siva
Siva

Reputation: 1519

Try the below sub query,

$query = "select o.email, 
          IFNULL( (select email from one where o.referrer = refer_link ),'') as refer_email, 
          (select count(referrer) from one where referrer = o.refer_link ) as refer_count 
          from one as o order by id  ";

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id SERIAL PRIMARY KEY
,email VARCHAR(50) NOT NULL
,referrer INT NULL
);

INSERT INTO my_table VALUES
(1,'[email protected]',NULL),
(2,'[email protected]',1),
(3,'[email protected]',2),
(4,'[email protected]',1),
(5,'[email protected]',4);

SELECT x.*, COUNT(y.id) refer_count FROM my_table x LEFT JOIN my_table y ON y.referrer = x.id GROUP BY x.id;
+----+-------------------+----------+-------------+
| id | email             | referrer | refer_count |
+----+-------------------+----------+-------------+
|  1 | [email protected]   |     NULL |           2 |
|  2 | [email protected] |        1 |           1 |
|  3 | [email protected] |        2 |           0 |
|  4 | [email protected] |        1 |           1 |
|  5 | [email protected]   |        4 |           0 |
+----+-------------------+----------+-------------+

Upvotes: 1

Related Questions