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 |
+----+-------------------+----------+-------------+

POPULAR ONLINE

Wordpress Divi Theme - Anchor link opens tab toggle

Check JSON object in java

Laravel 5.4 Keeps Logging user out

Ajax JSON request always sends none?

Echo shortcode in another shortcode

Select COUNT with inner join

I have two tables:

163

MySQL

"SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018' for column 'created_at' at row 1 (SQL: insert into `news`

469

MySQL

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

I have a database with the follow:

140

MySQL

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