sql - 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 master table has unique rows i.e. single record of users in system. each user can uniquely identified email_id in each table.

one user can have multiple profile, means 1 us_email_id field in tbluser table, there can many user_id in tbl_user, i.e there can multiple entries in second table each user.

now have select users have logged in last time before, lets '2012', i.e before 1-jan-2012.

but if 1 user has 2 or more user_id , 1 user_id has last_logged_date less 2012 other user_id has greater 2012 such user should ignored.

in last all result user marked deletion setting del_flag in master table ‘yes’ eg:

    record in tbl_master:     a123 ram@abc.com d234 no     a123 john@abc.com d256 no      record in tbl_user can like:     ram@abc.com  '11-dec-2011'   ram1     ram@abc.com  '05-apr-2014'   ram2     john@abc.com '15-dec-2010'  john1

in such case john's record should selected not of ram 1 profile has last_logged_date>1-jan-2012

another possibility was

select     m.m_email_id,     max(u.last_logged_date) last_login     tbl_master m inner join     tbl_user u on u.u_email_id = m.m_email_id group m.m_email_id having     -- year(max(u.last_logged_date)) < 2012   -- use appropriate function of dbms     extract(year from(max(u.last_logged_date))) < 2012 -- should version oracle  -- see http://docs.oracle.com/cd/b14117_01/server.101/b10759/functions045.htm#i1017161

your update operation can use select in where clause.


Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more