php Mysql Rows Merging from one table

Question

Is there a way to merge only the last personal_id's where i have values in the columns?
Lets say Anderson with the p_id 2 get married and his name changes.

id  p_id        last_name   job         Hobby
1   1           Smith       Programmer  NULL
2   2           Anderson    Influencer  Reading
3   2           McKing      Influencer  NULL
4   3           Jordan      Mother      Cooking
5   1           Smith       NULL        Soccer

For example that i get:

p_id: 1 - Smith - Programmer - Soccer
p_id: 2 - McKing - Influencer - Reading

Thanks in advance.

Update:

You guys are really strict.

I have tried a couple of queries but i can't achieve my goal.

SELECT * FROM table GROUP_BY p_id ORDER_BY id ASC

Is there a way to "merge" only the last personal_id's where i have values in the columns? Yes...what have you tried to achieve that? — B001ᛦ
– B001ᛦ, Commented Nov 9, 2017 at 9:54

Shashank Katiyar · Accepted Answer · 2017-11-09 16:51:46Z

1

CREATE TABLE QUEST  (
    id        INT NOT NULL,
    p_id      INT NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    job       VARCHAR(50),
    hobby     VARCHAR(50));  

INSERT INTO quest VALUES (1,1,'Smith','Programmer',NULL );
INSERT INTO quest VALUES (2,2,'Anderson','Influencer','Reading' );
INSERT INTO quest VALUES (3,2,'McKing','Influencer', NULL );
INSERT INTO quest VALUES (4,3,'Jordan','Mother','Cooking' );
INSERT INTO quest VALUES (5,1,'Smith',NULL,'Soccer' );
INSERT INTO quest VALUES (6,3,'Jordan','Mother','Cooking' );
INSERT INTO quest VALUES (7,4,'John',NULL,NULL );
INSERT INTO quest VALUES (8,5,'Mike','Test',NULL );
INSERT INTO quest VALUES (9,5,'Mick','Tester',NULL );
COMMIT;


SELECT q.p_id,
  q.last_name,
  (SELECT job FROM Quest WHERE job IS NOT NULL AND p_id = q.p_id ORDER BY id DESC LIMIT 1) job,
  (SELECT hobby FROM Quest WHERE hobby IS NOT NULL AND p_id = q.p_id ORDER BY id DESC LIMIT 1) hobby
FROM Quest q
WHERE id IN (SELECT MAX(id) FROM Quest GROUP BY P_Id )
ORDER BY p_id;


Result:
p_id - last_name - job - hobby
1 - Smith - Programmer - Soccer
2 - McKing - Influencer - Reading
3 - Jordan - Mother - Cooking
4 - John - NULL - NULL
5 - Mick - Tester - NULL

answered Nov 9, 2017 at 16:51

Shashank Katiyar

1392 bronze badges

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

1 Comment

dr.oetker Over a year ago

Really nice Solutions. And its working pretty nice - Thank You ! @ShashshankKatiyar

Lexxusss · Accepted Answer · 2017-11-09 10:10:58Z

1

SELECT t.last_name, t.job, t.hobby
 FROM {THE_TABLE) as t
 JOIN {THE_TABLE} as t2 on t2.p_id = t.p_id
WHERE t.job is not null
 AND t.hobby is not null

edited Nov 9, 2017 at 10:10

answered Nov 9, 2017 at 10:05

Lexxusss

5524 silver badges10 bronze badges

Collectives™ on Stack Overflow

php Mysql Rows Merging from one table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related