[DB-SIG] Mysql query

[Andy Todd]

Patricia wrote:
> Hi all,
> 
> I'm having a hard time trying to create this query.
> I have two tables:
> log - columns: id, name and date
> info - columns: id, tag, value
> 
> i.e.:
> log
> id | name                  | date
> -------------------------------------
> 1  | machine_name          | 07/31/06
> 2  | another_machine_name  | 08/01/06
> 
> info
> id  | tag       | value
> --------------------------------
> 1   | release   | release_name
> 1   | arch      | 32-bit
> 1   | mode      | amode
> 2   | release   | release_name
> 2   | arch      | 64-bit
> 2   | mode      | amode
> 
> Basically, the info table contains detailed information of 
> each machine in the log table. The same machine can appear 
> more than once, but with a different id number, that's why
> I use group by in the following query:
> 
>  select lo.name, inf.value, lo.date from log as lo 
>  join info as inf on lo.id=inf.id where (inf.tag='release' 
>  and inf.tag='arch') AND lo.id in (select max(id) 
>  from log group by name) order by name
> 
> Everything worked well, but now I've been asked to display
> this data based on "mode" (located in the info table). 
> I don't have to display mode, but I have to use it as 
> a condition since there are more than two modes. 
> I'm not sure how to add this condition to the query, though. 
> I tried adding this to the query above but, of course, 
> it didn't work:
>  
>  AND inf.id in (select id from info where value LIKE %s)
> 
> I'd appreciate any help
> Thanks,
> Patricia
> 
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig

Patricia,

This isn't a Python or MySQL problem, it's an issue with your schema
design. You need to change your schema. You could change your info table
to something less generic like;

id |  release     | arch   | mode
----------------------------------
1  | release_name | 32-bit | amode
2  | release_name | 64-bit | amode
3  | release_name | 64-bit | amode

This will make your initial query a lot simpler, something like;

select lo.name, inf.release, inf.arch, inf.mode, lo.date
from log as lo
      join info as inf on lo.id=inf.id
where lo.id in (select max(id)
                 from log group by name)
order by name

You could make it even simpler by putting a 'latest' flag on your log
table, e.g

id | name                  | date     | latest
----------------------------------------------
1  | machine_name          | 07/31/06 | Y
2  | another_machine_name  | 08/01/06 | N
3  | another_machine_name  | 08/02/06 | Y

then your query could look something like;

select lo.name, inf.release, inf.arch, inf.mode, lo.date
from log as lo
      join info as inf on lo.id=inf.id
where lo.latest = 'Y'
order by name

Having a generic table with key value pairs like your current info table
just leads to some really horrible SQL as you are currently finding. 
Unless you truly don't know what key-value pairs you are going to store 
it's always best to design your table to store the specific values you 
are going to store.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/