MySQL query without using subqueries

Discussions

EJB design: MySQL query without using subqueries

  1. MySQL query without using subqueries (5 messages)

    Hi, Please help me in finding a solution. User table user_id name city UserHistory table user_id date action Your SQL should be compatible with MySQL 4.0. Please avoid using subqueries. 1. Given the tables above, write an SQL query that returns the name, city and most recent date for any user that has logged in over the last 30 days (you can tell a user has logged in if the action field in UserHistory is set to “logged_on?). For example, every time a user logs in a new row is inserted into the UserHistory table with user_id, current date and action (where action = ‘logged_on?). 2.c. Given the tables above, write an SQL query to determine which user_ids in the User table are not contained in the UserHistory table (assume the UserHistory table has a subset of the user_ids in User table). Do not use the SQL MINUS statement. Note: the UserHistory table can have multiple entries for each user_id.

    Threaded Messages (5)

  2. Second query[ Go to top ]

    I know the second query: select distinct u.user_id from user u left join userhistory uh on u.user_id=uh.user_id where uh.user_id is null Viliam
  3. Thanks[ Go to top ]

    Thank you Villiam.
  4. Answer for the 1st one SELECT name, city, (SELECT date FROM UserHistory WHERE UserHistory.user_id = User.user_id ORDER BY date DESC LIMIT 1) AS date FROM User Mithat
  5. 2.c.? Is this some kind of homework problem? :-P I'll take a stab at #1 without using a subquery: select User.name, User.city, max(UserHistory.date) from User, UserHistory where User.user_id = UserHistory.user_id and UserHistory.action = 'logged_on' and UserHistory.date >= date_sub(curdate(), interval 30 day) That should get the most recent date/time that the user logged in. If you just want the most recent date, regardless of whether it's the date that they last logged in or not you'll need to join to UserHistory twice: once to determine if they've logged in over the past thirty days and once to get the absolute most recent date.
  6. Re: Forgot the group by[ Go to top ]

    I forgot the group by but it would just be a group by user_id.