MySQL, create view from columns from same table

126
September 28, 2018, at 12:20 PM

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       jerry@jerry.com         ref11111 
2       elaine@elaine.com       ref22222         ref11111
3       george@george.com       ref33333         ref22222
4       kramer@kramer.com       ref44444         ref11111 
5       cosmo@cosmo.com         ref55555         ref44444

How to create this view?

email                   refer_email         refer_count
--------------------------------------------------------
jerry@jerry.com                                2 
elaine@elaine.com       jerry@jerry.com        1
george@george.com       elaine@elaine.com      0
kramer@kramer.com       jerry@jerry.com        1 
cosmo@cosmo.com         kramer@kramer.com      0

Thank you very much for help!

Answer 1

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  ";
Answer 2
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,'jerry@jerry.com',NULL),
(2,'elaine@elaine.com',1),
(3,'george@george.com',2),
(4,'kramer@kramer.com',1),
(5,'cosmo@cosmo.com',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 | jerry@jerry.com   |     NULL |           2 |
|  2 | elaine@elaine.com |        1 |           1 |
|  3 | george@george.com |        2 |           0 |
|  4 | kramer@kramer.com |        1 |           1 |
|  5 | cosmo@cosmo.com   |        4 |           0 |
+----+-------------------+----------+-------------+
Rent Charter Buses Company
READ ALSO
Select COUNT with inner join

Select COUNT with inner join

I have two tables:

163
List movies directed by one of the movie actors [on hold]

List movies directed by one of the movie actors [on hold]

I have a database with the follow:

140
MySQL Query with insert specific data

MySQL Query with insert specific data

I would like to replace/save data from another table into my WordPress databaseI wonder if you can write this one query or do it with PHP (this way is much slower !!) I would like the query to be:

150