Single MySQL statement

Discussion in 'Programming' started by tarponkeith, Dec 21, 2007.

0
  1. #1
    Can anyone turn these statements, into a single query using JOIN... Or even a subquery if it would help with system resources?

    select id, title from table1 where (langs like '%$z_lang%');
// for each result, do this query:
select count(*) from table2 where (lang = '$z_lang') and (category = ^ID_FROM_1ST_QUERY) and (status = 'active');
    PHP:
    I'll give... $4... to the first one that sends me a working sql statement :)
     
    tarponkeith, Dec 21, 2007 IP
  2. Synch

    Synch Peon

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #2 
    
select a.id, a.title, b.count(*) from 
table1 a
inner join 
table2 b
on a.id=b.category
where b.lang = '$z_lang' and b.status= 'active'
group by a.id, a.title;
    PHP:

    For better performance:
    Add an index to id and title in table1
    and status, category, and langs in table2

    Keep the $4.
     
    Synch, Dec 21, 2007 IP
    ansi and tarponkeith like this.