×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I know this query can be done!

I know this query can be done!

I know this query can be done!

(OP)
I have 3 tables - the first table, called se_submission_log, containing configurations of URLs and dates, identified by a unique field called submit_id. Here is a sample from this table:

se_submission_log
+-----------+--------------+-------------+
| submit_id | url          | date_submit |
+-----------+--------------+-------------+
|         1 | www.url1.com | 2000-11-22  |
|         2 | www.url2.com | 2000-11-21  |
|         3 | www.url3.com | 2000-10-02  |
|         4 | www.url4.com | 2000-12-04  |
|         5 | www.url5.com | 2001-01-31  |
+-----------+--------------+-------------+

The second table, called engines, is an engines table which lists off search engine information, most importantly engine_name, and all engines are identified by a unique field called engine_id. Here is a sample from that table:

engines
+-----------+-------------+
| engine_id | engine_name |
+-----------+-------------+
|         3 | AltaVista   |
|         4 | Excite      |
|         5 | AOL         |
|         6 | HotBot      |
|         7 | LookSmart   |
+-----------+-------------+

The last table is called se_submitengines, and simply contains the fields submit_id and engine_id. Here's the final sample:

se_submitengines
+-----------+-----------+
| submit_id | engine_id |
+-----------+-----------+
|         1 |         2 |
|         1 |         4 |
|         1 |         6 |
|         2 |         2 |
|         3 |         4 |
+-----------+-----------+


Now, what I am trying to do is to take a defined set of rows from the se_submission_log (we'll say submit_id 1 and 2), and find the most recent date_submit for each engine used, only showing each engine once.

The following query will display part of that goal, except that it repeats engine names if there is more than one entry associated with it:

SELECT se.url,se.date_submit,e.engine_name FROM engines e LEFT JOIN se_submitengines j ON j.engine_id = e.engine_id LEFT JOIN se_submission_log se ON se.submit_id = j.submit_id WHERE se.submit_id = 1 OR se.submit_id = 2 ORDER BY date_submit DESC;

It returns something like this:
+--------------+-------------+----------------+
| url          | date_submit | engine_name    |
+--------------+-------------+----------------+
| www.url1.com | 2000-11-21  | Lycos          |
| www.url1.com | 2000-11-21  | Northern Light |
| www.url1.com | 2000-08-04  | Lycos          |
| www.url1.com | 2000-10-13  | Northern Light |
| www.url2.com | 2000-11-21  | Northern Light |
| www.url2.com | 2000-09-11  | Northern Light |
+--------------+-------------+----------------+

Now, the goal is to have the table above except filtered a bit so it only shows this:

+--------------+-------------+----------------+
| url          | date_submit | engine_name    |
+--------------+-------------+----------------+
| www.url1.com | 2000-11-21  | Lycos          |
| www.url1.com | 2000-11-21  | Northern Light |
| www.url2.com | 2000-11-21  | Northern Light |
+--------------+-------------+----------------+

If necessary, multiple queries can be used, but only if it's one query per URL. Thanks and happy puzzling.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close