RJP1

Ignore the user table, it's simply a check to see if the user exists...

Well, they dont have to be different, I just need a little help to multiply 2 counts from 2 different tables really.

Essentially I need to combine these 2 queries: SELECT p.userid, COUNT(p.status) AS score FROM table1 AS p WHERE (SELECT COUNT(*) FROM usertable WHERE id = p.userid)>0 AND p.date_joined >= 0 AND p.date_joined <= ".time(); GROUP BY p.userid ORDER BY score DESC LIMIT 10"; And SELECT userid, (1 + (COUNT(*) * 0.1)) AS score2 FROM table2 WHERE time >= 0 AND time <= FROM_UNIXTIME($now) GROUP BY userid ORDER BY score2 DESC Basically, I'd like to get score and score2 and multiply them together in 1 query... Doable? Cheers, RJP1

Hi guys, I'm looking to get a little help on a complex issue... I have 2 tables. Both have user ids. I'd like to make a leader board from the data within each table... I need to count each row in one table and get that as a simple number per user. Then for each user id, get the count from another table and then multiply these 2 figures together. Then order by the "score" DESC... Can anyone help me find the best way to do this? Thanks! RJP1

Well I'd love to, I just don't know how - hence my asking here! Cheers, RJP1

Can anyone help with this one? I'm struggling big time! RJP1

How do you suggest I do that within my existing query? Could you possibly point me in the right direction? Cheers! RJP1

Not as such, I want the days that haven't got a value to have the value of the first day previous to the zero day if that makes sense! In this case 2011-03-11, 2011-03-12, 2011-03-13, 2011-03-14 would = 10000 (because 2011-03-10 = 10000) and 2011-03-16, 2011-03-17, 2011-03-18, 2011-03-19 and 2011-03-20 to = 10345.47 (because 2011-03-15 = 10345.47). Doable? RJP1

Hi guys, I have a simple table like so: id timestamp value -------------------------------------- 30 1299784130 10000.00 31 1300168969 10500.00 Using the following SQL query I am able to get the higest value per day in a date range including days that don't exist in the database (for graphing): SELECT Y.day, MAX(COALESCE(Z.value,0)) as value FROM (SELECT DATE_ADD( '2011-01-01', INTERVAL a.i + b.i *10 + c.i *100 DAY ) AS DAY FROM integers a CROSS JOIN integers b CROSS JOIN integers c HAVING DAY BETWEEN '2011-03-10' AND '2011-03-20') Y LEFT OUTER JOIN `table` Z ON Y.day = DATE(FROM_UNIXTIME(Z.timestamp)) GROUP BY Y.DAY ORDER BY Y.DAY ASC This yields: day value -------------------------------- 2011-03-10 10000.00 2011-03-11 0.00 2011-03-12 0.00 2011-03-13 0.00 2011-03-14 0.00 2011-03-15 10345.47 2011-03-16 0.00 2011-03-17 0.00 2011-03-18 0.00 2011-03-19 0.00 2011-03-20 0.00 Which is lovely. However, can someone advise how I alter the query to ensure the dates between 2011-03-10 and 2011-03-15 have 10000.00 as their value rather than zero? In other words, if the value comes out as zero, can you help me make it check backwards in time to check for the highest previous value? Thanks! RJP1

Because that's how I did it. I wasn't able to do it in one, hence my asking here.

Sure, this is what I did: function getCategories() { $db =& JFactory::getDBO(); $query = 'SELECT DISTINCT c.parentid FROM #__vault as i,jos_vault_categories as c WHERE i.catid=c.id AND c.parentid>0 AND i.published=1'; $db->setQuery( $query ); $parentidswithchildren = $db->loadObjectList(); if ($parentidswithchildren) { $cat_ids = array(); foreach ( $parentidswithchildren AS $c ) { $cat_ids[] = $c->parentid; } $in = "c.id IN (".implode(',',$cat_ids).") OR "; } else { $in = null; } $query = "SELECT c.* FROM #__vault_categories AS c WHERE [b]$in[/b](SELECT COUNT(catid) FROM #__vault WHERE catid=c.id AND published=1) > 0 AND parentid=0"; $db->setQuery( $query ); $categories = $db->loadObjectList(); return $categories; } If there are children, it puts their id's into a comma separated list as $in. Works well. What do you think? RJP1

What I meant by runaway categorisation was: Parent - child 1 - child 1's child - etc - child 2 But I have figured out how to prevent this in my application, but I have yet to figure out how to get a list of parents then each parents child categories like so: - parent - child - child - parent - parent - parent - child etc I'll have a look at it now and see if I can figure it out, I managed the harder query in my other post you thought this was like. They are similar and I apologise for the seemingly double post. Cheers, RJP1

Yes, but it's not. This issue I figured out. Cheers, RJP1

Hi guys, I have a sql query that selects categories as long as they are a parent and they have items published within it: SELECT c.* FROM #__categories AS c WHERE (SELECT COUNT(catid) FROM #__items WHERE catid=c.id AND published=1) > 0 AND parentid=0 ORDER BY name ASC However, I'm having difficulty combining the above query to also get subcategories that are within a parent category but only when the subcategory has items in it and not its parent. I want to get these "empty" parents if they contain subcategories with items in them... possible? Cheers, RJP1

Hi guys, I'm building a category table in Joomla and have this so far: id | name | parentid ------------------------- 1 | cat1 | 2 2 | cat2 | 0 3 | cat3 | 0 As you can see, categories 2 and 3 are parents (parentid = 0) but cat 1 is categorised under cat 1. What is the best way to select subcategories straight after it's parent? E.g. cat2, cat1, cat3 to then go on to put them in a table. Would it be best to prevent runaway categorisation and just allow categories and subcategories? Cheers for the guidance. RJP1