Join GROUP ORDER problem

Nells

I have a couple of tables that I am using, first is the Device Table (device2_table.png)
It contains the pieces and any unique specifications about the DEVICE...

Next is codelevels, this table contains the NAME of the device, and the CODES as they are released...
(a DESC sort and grouping is what I thought would be the way to list...)

This is the DESIRED output....
(desired_RESULTS.png)

=========================================================================

The Results that I'm getting (with the following, Im_getting.png)

      include("../Connections/dbcon.php");
      $sql="SELECT * FROM codelevel3 AS c JOIN device2 AS d USING (name,cont)  
      where (c.name LIKE d.name or c.cont LIKE d.cont)
      GROUP BY c.name, c.cont, d.Dev_T
      ORDER BY d.sortfield,  c.firmware DESC, c.name DESC, c.Date DESC";
      $result = mysql_query($sql);

It appears to hang at a specific FIRMWARE version, which is not the MOST CURRENT... (for that device)

======================================================================
I know - (data_exists.png)
The data is contained, in the DB and can be retrieved, with the most CURRENT code sorted correctly by selecting ONE device
with this code... (not grouped)

      include("../Connections/dbcon.php");
        $sql="SELECT name, firmware, NVSRAM, cont, Date FROM codelevel3 AS c
        WHERE (c.`name` LIKE 'ds48%') and (c.NVSRAM >'-')
        ORDER BY c.`Date` DESC, c.`name`";
      $result = mysql_query($sql);

=======================================================================

The following, achieves the desired result...(desired_results.png)
(requires MANUAL intervention each time there is a NEW CODE released, which
occurs, at least once a quarter, or MORE....)

      include("../Connections/dbcon.php");
      $sql="SELECT * FROM codelevel3 AS c JOIN device2 AS d USING (cont, name)  
      where (c.name=d.name 	and c.cont = d.cont and c.Date >'2008-06-28'
      and c.cont >'0')
      or (c.firmware='05.30.25.00' or c.name='DS4100')
      or (c.name='ds4300' and c.cont='1'and c.firmware ='05.34.10.00')
      or (c.name='ds4300' and c.cont='2'and c.firmware ='06.60.08.00')
      or (c.name='ds4400' and c.cont='2'and c.firmware ='06.12.56.00')	 
      or (c.name='DS4500' and c.cont='2'and c.firmware ='06.60.08.00')
      or (c.name='DS4700' and c.cont='2'and c.firmware ='07.15.07.00')
      or (c.name='DS4800' and c.cont='2'and c.firmware >'07.15.00.00')
      or (c.name='DS4200' and c.cont='2'and c.firmware ='07.15.07.00')      
      or (c.name='exp100' and c.firmware='9566')
      or (c.name='exp500' and c.cont='0')
      or (c.name='exp700' and c.cont='0')
      or (c.name='exp710' and c.cont='0'and c.firmware ='9681')
      or (c.name='exp420' and c.cont='0'and c.firmware ='98b5')
      or (c.name='exp810' and c.cont='0'and c.firmware ='98b5')
      GROUP by cont,name
      ORDER by sortfield";
      $result = mysql_query($sql);

But as I turn this over to another member of the group, it becomes a hassle to ADD NEW devices, and have the most current versions appear in the list.

Any Suggestions ??

Nells

Sorry.. Tried to edit the post, but the edit option didn't exit.

I thought this would be a fairly simple GROUP and ORDER, but I can not get it to work, without explicitly telling the WHERE clause the and / or parameters.

The DEVICE table has all of the devices listed, and the JOIN appears to be working to the CODE_LEVEL table. But when I try to ORDER after the GROUP, it displays a row that does NOT contain the most current version of that DEVICE.

I have tried to include a picture of the database tables that I have created, and the script that I have assembled -
(pieced together from reading posts on this community over the last year, I am totally green to php).

There have to be something wrong with the DATA_TYPE of the table or something that I am overlooking in the SQL QUERY.

Any ideas would be appreciated.

Thanks in advance.