Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 14th, 2003, 05:19 PM
eln eln is offline
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default two tables, one query, getting the latest for each

sbj: two tables, one query, getting the latest for each in tbl1 from tbl2
hi,
Could you please help me with this?
Say I have two tables

tbl1 {
id int auto_increment,
name string,
description string
}

tbl2 {
id int,
file_url int,
version int
}

These two tables are used for software project releases on a web site,
`tbl1` keeps the project `name`, `description`, and auto inc. `id` and the `tbl2` keeps files released for a project, `id` references `tbl1.id`, the file_url is the file name that would be presented for a given `version`. So as you might guessed a project once is inserted into tbl1 then each time a file is released for a given project its name along with its version is inserted into the tbl2 referencing tbl1.id, so in the end e.g. Project1 Project2 Project3 each has ~20 files versions start with 0 up to say 20, my question is; executing only __one__ query, how do I get the latest (highest version) file name for a given project _and_ optinaly for all projects, _again_ only one query. Feel free to alter the tables, in a sense all I need to know is how/is it possible/ what needs to be used.

Thanks

P.S Hope you understand my English
Reply With Quote
  #2 (permalink)  
Old November 17th, 2003, 02:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

To list latest version of all projects.

select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id
group by name order by name
______________________________________________

To list the Latest version of a project provided the PROJECT_NAME or PROJECT_ID

select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id and tbl1.name=<PROJECT_NAME>
                     OR
select name, max(version) from tbl1, tbl2
where tbl1.id=tbl2.id and tbl1.id=<PROJECT_ID>
______________________________________________

Hope that helps

Cheers,


-Vijay G
Reply With Quote
  #3 (permalink)  
Old November 17th, 2003, 10:15 PM
eln eln is offline
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay,
thanks for the help.

could you take a look at this
below is the `tbl2` altered and called `files` now and `tbl1` is called `downloads` now.

Create Table: CREATE TABLE `files` (
 `id` int(10) NOT NULL auto_increment,
 `download_id` int(10) default NULL,
 `posted` timestamp(14) NOT NULL,
 `version` int(6) default NULL,
 `url` varchar(255) default NULL,
 PRIMARY KEY (`id`),
 KEY `download_id` (`download_id`),
 KEY `version` (`version`)
 TYPE=MyISAM



then I load it up like:

+-------------+---------+------------+----------+
| download_id | version | url | pr |
+-------------+---------+------------+----------+
| 2 | 1 | shex_file1 | 03:56:38 |
| 2 | 2 | shex_file2 | 03:56:46 |
| 2 | 3 | shex_file3 | 03:56:50 |
| 2 | 4 | shex_file4 | 03:56:53 |
| 2 | 5 | shex_file5 | 03:56:57 |
| 2 | 6 | shex_file6 | 03:57:02 |
| 10 | 1 | prj10_1 | 03:59:36 |
| 11 | 5 | prj11_5 | 03:59:27 |
| 11 | 4 | prj11_4 | 03:59:23 |
| 11 | 3 | prj11_3 | 03:59:18 |
| 11 | 2 | prj11_2 | 03:59:06 |
| 11 | 1 | prj11_1 | 03:58:53 |
| 10 | 2 | prj10_2 | 03:59:39 |
| 10 | 3 | prj10_3 | 03:59:43 |
+-------------+---------+------------+----------+

SELECT url, name, max(version), DATE_FORMAT( posted , '(%M %D, %Y %h:%i:%s)' ) AS posted_humane FROM files, downloads WHERE downloads.id=files.download_id GROUP BY downloads.id ORDER BY downloads.id;

I don't understand, it returns latest `version` but not `url` corresponding to it, please see below:

+------------+---------+--------------+--------------------------------+
| url | name | max(version) | posted_humane |
+------------+---------+--------------+--------------------------------+
| shex_file1 | shex | 6 | (November 18th, 2003 03:56:38) |
| prj10_1 | zzzzzh | 3 | (November 18th, 2003 03:59:36) |
| prj11_5 | atoolsh | 5 | (November 18th, 2003 03:59:27) |
+------------+---------+--------------+--------------------------------+
could you help??
Thank you very much!!

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query for getting latest date and following .. dragonball SQL Server 2000 4 February 28th, 2008 07:10 AM
SQL Query - picking latest record and group by markw SQL Language 2 April 6th, 2005 03:54 AM
Query to Select latest X entries acdsky Classic ASP Databases 3 July 6th, 2004 01:41 AM
Latest Date with Inner Join Query rstelma SQL Server 2000 3 November 5th, 2003 06:32 AM
Returning ONLY the latest records in a query kilkerr1 Access 3 November 3rd, 2003 12:03 PM



All times are GMT -4. The time now is 10:30 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.