Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I know this query can be done!

Status
Not open for further replies.

gr8gonzo

MIS
Feb 6, 2001
21
US
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 | | 2000-11-22 |
| 2 | | 2000-11-21 |
| 3 | | 2000-10-02 |
| 4 | | 2000-12-04 |
| 5 | | 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 |
+--------------+-------------+----------------+
| | 2000-11-21 | Lycos |
| | 2000-11-21 | Northern Light |
| | 2000-08-04 | Lycos |
| | 2000-10-13 | Northern Light |
| | 2000-11-21 | Northern Light |
| | 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 |
+--------------+-------------+----------------+
| | 2000-11-21 | Lycos |
| | 2000-11-21 | Northern Light |
| | 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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top