CODEWITHSUNDEEP
phpmysql
user9437856
user9437856

Reputation: 2388

Display the latest records from the right table and if records not available then at least show the records from left table

I have two table

  1. tbl_register
  2. tbl_societymyprofile

What I am doing is, I have to display the records from the tbl_register table and I have to display the latest record from the tbl_societymyprofile table.

I have a tried the below query.

SELECT *
FROM   tbl_register AS r
       LEFT JOIN tbl_societymyprofile AS m
              ON r.reg_id = m.reg_id
WHERE  r.reg_id =(SELECT Max(secretary_id) AS c_id
                   FROM   tbl_societymyprofile
                   WHERE  reg_id = 17
                   GROUP  BY reg_id DESC)
       AND r.is_active = 1

Also if the records are not available in the tbl_societymyprofile then at least display the records from the tbl_register table.

reg_id id is the primary id of the tbl_register table. As of now I have the records in the tbl_register table but don't have the records in the tbl_societymyprofile and i am getting the empty records

tbl_register

reg_id | name | mobileno  | email     
1      | abc  |1234123455 |  [email protected]
2      | xyz  |1234345455 |  [email protected]

tbl_societymyprofile

secretary_id | reg_id | sec_name | sec_email     | date_of_added
1            | 1      | ncx      | [email protected] | 2021-06-09 15:54:35
2            | 1      | xbs      | [email protected] | 2021-06-09 15:55:18

would you help me out with this?

Upvotes: 0

Views: 43

Answers (2)

Amit Verma
Amit Verma

Reputation: 2490

If you need all records, try the following query:

SELECT r.*, 
       X.*
     FROM tbl_register AS r
LEFT JOIN (SELECT sp.* 
                 FROM tbl_societymyprofile sp
           INNER JOIN (SELECT reg_id, 
                              MAX(secretary_id) secretary_id
                       FROM tbl_societymyprofile
                       GROUP BY reg_id )T 
                   ON sp.reg_id = T.reg_id 
                  AND sp.secretary_id = T.secretary_id) X 
       ON r.reg_id = X.reg_id

If you need specific records, try the following query instead:

SELECT r.*, 
       X.*
FROM      tbl_register AS r
LEFT JOIN (SELECT sp.* 
             FROM     tbl_societymyprofile sp
           INNER JOIN (SELECT reg_id, 
                              MAX(secretary_id) secretary_id
                       FROM tbl_societymyprofile
                       GROUP BY reg_id           )T 
                   ON sp.reg_id = T.reg_id 
                  AND sp.secretary_id = T.secretary_id) X 
       ON r.reg_id = X.reg_id
WHERE r.reg_id = 2

Upvotes: 1

StackSlave
StackSlave

Reputation: 10627

If your joined table order is not important (it usually isn't important) then maybe you should try something like:

SELECT * FROM tbl_register t LEFT JOIN tbl_societymyprofile s USING(reg_id) WHERE t.reg_id=2 ORDER BY s.date_of_adder DESC LIMIT 1;

Here's a fiddle. See Query #3.

Upvotes: 1

Related Questions