CODEWITHSUNDEEP
sqloracle-databaseplsql
SKChauhan
SKChauhan

Reputation: 27

Selectively retrieve data from tables when one record in first table is linked to multiple records in second table

I have 2 tables:

1.  Tbl_Master: columns:
     a. SEQ_id
     b. M_Email_id
     c. M_location_id
     d. Del_flag

2.  Tbl_User: columns
     a. U_email_id
     b. Last_logged_date
     c. User_id

First table Is master table it has unique rows i.e. single record of all users in the system. Each User can be uniquely identified by the email_id in each table.

One user can have multiple profile, which means for one us_email_id field in the tblUser table, there can be many user_id in tbl_User, i.e there can be multiple entries in second table for each user.

Now I have to select only those users who have logged in for last time before, lets say '2012', i.e before 1-Jan-2012.

But if one user has 2 or more user_id and one user_id has last_logged_date less than 2012 But other user_id has greater than 2012 then such user should be ignored.

In the last all all the result user will be marked for deletion by setting DEL_flag in master table to ‘Yes’ For eg:

    Record in Tbl_Master:
    A123 [email protected] D234 No
    A123 [email protected] D256 No

    Record in tbl_User can be Like:
    [email protected]  '11-Dec-2011'   Ram1
    [email protected]  '05-Apr-2014'   Ram2
    [email protected] '15-Dec-2010'  John1

In such case only John's Record should be selected not of Ram whose one profile has last_logged_date>1-Jan-2012

Upvotes: 1

Views: 159

Answers (2)

VMai
VMai

Reputation: 10346

Another possibility was

SELECT
    m.M_Email_id,
    MAX(u.Last_logged_date) AS last_login
FROM
    Tbl_Master m
INNER JOIN
    Tbl_User u on u.U_email_id = m.M_Email_id
GROUP BY m.M_Email_id
HAVING
    -- Year(MAX(u.Last_logged_date)) < 2012   -- use the appropriate function of your DBMS
    EXTRACT(YEAR FROM(MAX(u.Last_logged_date))) < 2012 -- should be the version for oracle
 -- see http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions045.htm#i1017161

Your UPDATE operation can use this select in the WHERE clause.

Upvotes: 4

AK47
AK47

Reputation: 3807

Try this, this ans is in sql server, I haven't worked on Oracle.

select * from Tbl_Master 
outer apply
(
    select U_email_id,max(Last_logged_date)as LLogged,count(U_email_id) as RecCount 
    from Tbl_User 
    where Tbl_User.U_email_id = Tbl_Master.M_Email_id
     group by U_email_id

)as a
where RecCount >2
and Year(LLogged) < '2012'

Try this DEMO

Hope it helps you.

Upvotes: 1

Related Questions