Hello,
do any body can help me, i have problem with mysql syntax may be.
Let's first explain. I have two servers that i store filename, size
... in records of video camera files.
I have one database with two tables:
Servers and Files;
Servers contain:
ServerID, ServerName, User, Password, LastConnect, Port
and Files contain:
ServerID, FileName, Date, Size
Here are a full record:
mysql> SELECT A.ServerID, ServerName, User, Password, LastConnect,
FileName, Date, Size, Port FROM Server A, Files B WHERE A.ServerID =
B.ServerID AND FileName LIKE '%camera%' AND (FileName LIKE '%.avi' or
FileName LIKE '%.mpeg' or FileName LIKE '%.mpg' or FileName LIKE
'%.asf' or FileName LIKE '%.mpe' or FileName LIKE '%.asx' or FileName
LIKE '%.wmv' or FileName LIKE '%.mov' or FileName LIKE '%.qt') ORDER
BY Password;
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
| ServerID | ServerName | User | Password | LastConnect |
FileName | Date
| Size | Port |
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 1 CD1.avi |
2003-04-29 00:00:00 | 731107328 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 1 CD2.avi |
2003-04-29 00:00:00 | 707667968 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 2 CD1.avi |
2003-04-29 00:00:00 | 734191616 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 2 CD2.avi |
2003-04-29 00:00:00 | 733358080 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera 2/new/Camera Incident 1.avi |
2004-06-06 19:55:00 | 738500608 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Unsorted/Dublicate Camera Incident 1.avi |
2004-06-07 05:32:00 | 738500608 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2003/Videos/Camera Japan.avi |
2004-02-26 17:53:00 | 735176704 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2001/Camera 1 CD1-audio.avi |
2004-01-01 16:07:00 | 682442752 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2001/Camera 1 CD2-audio.avi |
2004-01-01 17:36:00 | 682225664 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2002/Camera 2 CD1.avi |
2004-01-02 09:45:00 | 732430336 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2002/Camera 2 CD2.avi |
2004-01-02 09:50:00 | 720777216 | 21 |
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
I want to group files that don't show in php dublicate files.
Dublicate files will be analysed by Size and frist to show files that
in server aaa.tv and trying:
mysql> SELECT A.ServerID, ServerName, User, Password, LastConnect,
FileName, Date, Size, Port FROM Server A, Files B WHERE A.ServerID =
B.ServerID AND FileName LIKE '%camera%' AND (FileName LIKE '%.avi' or
FileName LIKE '%.mpeg' or FileName LIKE '%.mpg' or FileName LIKE
'%.asf' or FileName LIKE '%.mpe' or FileName LIKE '%.asx' or FileName
LIKE '%.wmv' or FileName LIKE '%.mov' or FileName LIKE '%.qt') GROUP
BY Size ORDER BY Password;
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
| ServerID | ServerName | User | Password | LastConnect |
FileName | Date
| Size | Port |
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 1 CD1.avi |
2003-04-29 00:00:00 | 731107328 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 1 CD2.avi |
2003-04-29 00:00:00 | 707667968 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 2 CD1.avi |
2003-04-29 00:00:00 | 734191616 | 21 |
| 1 | ftp.aaa.tv | anonymous | aaa | 2004-06-09 02:01:07 |
Camera/Test/Camera 2 CD2.avi |
2003-04-29 00:00:00 | 733358080 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Unsorted/Dublicate Camera Incident 1.avi |
2004-06-07 05:32:00 | 738500608 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2003/Videos/Camera Japan.avi |
2004-02-26 17:53:00 | 735176704 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2001/Camera 1 CD1-audio.avi |
2004-01-01 16:07:00 | 682442752 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2001/Camera 1 CD2-audio.avi |
2004-01-01 17:36:00 | 682225664 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2002/Camera 2 CD1.avi |
2004-01-02 09:45:00 | 732430336 | 21 |
| 2 | ftp.bbb.tv | anonymous | bbb | 2004-06-09 02:00:27 |
Camera/Test/2002/Camera 2 CD2.avi |
2004-01-02 09:50:00 | 720777216 | 21 |
+----------+------------+-----------+----------+---------------------+----------------------------------------------------------------+---------------------+-----------+------+
But record Camera Incident 1.avi missing, and i show "Dublicate Camera
Incident 1.avi"
How i say to my mysql to group files by size and if file is dublicated
with priority is server one (aaa.tv) not server two?
Regards
P.S. Sorry of my bad english
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
