On Tue, August 4, 2015 16:05, Ryan Coleman wrote:
No but there should be. If there's not my task is useless.
Secondly yes. Unique name on it too.
--
Ryan Coleman
Publisher, d3photography.com
ryan.cole...@cwis.biz
m. 651.373.5015
o. 612.568.2749
On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote:
On Tue, August 4, 2015 11:19, Ryan Coleman wrote:
I have been a MySQL user and supporter for over a decade (since 2001)
and
I am almost ashamed to admit that I haven’t the faintest idea on how to
do
joins and unions.
I have a specific query I would love to run…
I have two tables, one with Unique data (“images”) and one with
corresponding paths but many replicated records (“files”).
I want to run a query that takes the results from /images/ and also
searches /images.file/ as a LIKE statement from /files.path/, sort by
/files.filesize/ in descending order returning just the first record
(largest file size). There may be up to 750 records from /images/ and
thusly could be 3000+ from /files/.
How on earth do I do this?
—
Ryan
First question, will there always be at least one record in the files
table for every record in the images table? That controls the kind of
join you will use. I don't think that a union is a player. Also, is
there
a unique record ID in each of the table?
Sorry, been swamped. If you can ignore the cases where there are not any
entry in the 'Files' table then a simple join will work. Otherwise you
would need a LEFT JOIN
Assume a structure
images.id - unique record ID
images.commonName - this will be the common reference name in both
...
files.id - unique record ID
files.commmonName - this will be the common reference name in both
files.location -where this record is on the disk/system
files.filesize - numeric field in whatever units you want (say bytes)
select images.commonName, files.location, MAX(files.filesize)
FROM images, files
WHERE images.commonName = files.commonName
GROUP BY files.commonName
ORDER BY images.commonName
Here is my test structure. No doubt someone else can get it optimized.
This seems to use a temp table
--
-- Table structure for table `files`
--
CREATE TABLE `files` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CommonName` varchar(255) NOT NULL,
`Location` varchar(255) NOT NULL,
`filesize` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `CommonName` (`CommonName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `files`
--
INSERT INTO `files` (`ID`, `CommonName`, `Location`, `filesize`) VALUES
(1, 'Image1', 'FileLoc/1/image1.jpg', 1000),
(2, 'Image1', 'FileLoc/2/image1.jpg', 5),
(3, 'Image2', 'FileLoc/1/image2.jpg', 25000),
(4, 'Image2', 'FileLoc/2/image2.jpg', 5000);
--
--
-- Table structure for table `images`
--
CREATE TABLE `images` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CommonName` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `CommonName` (`CommonName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `images`
--
INSERT INTO `images` (`ID`, `CommonName`) VALUES
(1, 'Image1'),
(2, 'Image2');
Hope this helps a bit.
---
William R. Mussatto, Senior Systems Engineer
http://www.csz.com
Ph. 909-920-9154 ext 101
Direct: 909-962-8547
__
CONFIDENTIALITY NOTICE:
This communication, including attachments, is for the exclusive use of the
person or entity to which it is addressed and may contain confidential,
proprietary and/or privileged information. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this by mistake, please contact the sender
immediately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
