query email, name, ip_address, referrer, user_agent, path

Question

so I have this query but I want a result like this

email	name	ip_address	referrer	user_agent	path
john@test.com	John	127.0.0.1	google.com	Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3	sample1.png
					sample2.png
					sample3.png
john@test.com	John	127.0.0.1	google.com	Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3	sample1.png
					sample2.png
					sample3.png

SQL query

SELECT u.*,
case when m.meta_key = "ip_address" then m.meta_value end as ip_address,
case when m.meta_key = "referrer" then m.meta_value end as referrer,
case when m.meta_key = "user_agent" then m.meta_value end as user_agent,
i.url_path
FROM tbl_users AS u  
RIGHT JOIN tbl_users_meta AS m ON m.user_id = u.id
RIGHT JOIN tbl_users_images AS i ON m.user_id = i.id

Output

id	email	name	ip_address	referrer	user_agent	path
1	john@test.com	John	127.0.0.1			sample1.png
1	john@test.com	John		google.com		sample1.png
1	john@test.com	John			Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3	sample1.png
2	jane@test.com	jane	127.0.0.1			sample2.png
2	jane@test.com	jane		google.com		sample2.png
2	jane@test.com	jane			Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3	sample2.png
						sample3.png
						sample4.png
						sample5.png
						sample6.png

for tables

-- create
CREATE TABLE tbl_users ( id INTEGER PRIMARY KEY, email TEXT NOT NULL, name TEXT NOT NULL );

-- insert
INSERT INTO tbl_users VALUES (0001, 'john@test.com', 'John');
INSERT INTO tbl_users VALUES (0002, 'jane@test.com', 'Jane');

-- create
CREATE TABLE tbl_users_meta ( id INTEGER PRIMARY KEY, user_id INTEGER, meta_key TEXT NOT NULL, meta_value TEXT NOT NULL );

-- insert
INSERT INTO tbl_users_meta VALUES (0001, 1, 'ip_address', '127.0.0.1');
INSERT INTO tbl_users_meta VALUES (0002, 1, 'referrer', 'google.com');
INSERT INTO tbl_users_meta VALUES (0003, 1, 'user_agent', 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3');
INSERT INTO tbl_users_meta VALUES (0004, 2, 'ip_address', '127.0.0.1');
INSERT INTO tbl_users_meta VALUES (0005, 2, 'referrer', 'google.com');
INSERT INTO tbl_users_meta VALUES (0006, 2, 'user_agent', 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.3');

-- create
CREATE TABLE tbl_users_images ( id INTEGER PRIMARY KEY, user_id INTEGER, url_path TEXT NOT NULL );

-- insert
INSERT INTO tbl_users_images VALUES (0001, 1, 'sample1');
INSERT INTO tbl_users_images VALUES (0002, 1, 'sample1');
INSERT INTO tbl_users_images VALUES (0003, 1, 'sample1');
INSERT INTO tbl_users_images VALUES (0004, 2, 'sample1');
INSERT INTO tbl_users_images VALUES (0005, 2, 'sample1');
INSERT INTO tbl_users_images VALUES (0006, 2, 'sample1');

Would you settle for something like `sample1.png,sample2.png` in the final column? If so, look into `GROUP_CONCAT`. — Barry Carter, Jul 11 '22 at 15:48

score 0 · Answer 1 · answered Jul 11 '22 at 17:48

Here is the solution:

SELECT 
    t.email, t.name, t.ip_address, t.referrer, t.user_agent, 
    i.url_path 
FROM(
    SELECT 
        u.*,
        (SELECT mip.meta_value FROM tbl_users_meta AS mip WHERE mip.meta_key = "ip_address" AND mip.user_id = u.id) AS ip_address,
        (SELECT mref.meta_value FROM tbl_users_meta AS mref WHERE mref.meta_key = "referrer" AND mref.user_id = u.id) AS referrer,
        (SELECT magent.meta_value FROM tbl_users_meta AS magent WHERE magent.meta_key = "user_agent" AND magent.user_id = u.id) AS user_agent
    FROM tbl_users AS u 
)
AS t
LEFT JOIN tbl_users_images AS i ON t.id = i.user_id;

And output of above would be:

query email, name, ip_address, referrer, user_agent, path

SQL query

Output

for tables

1 Answers1