MYSQL: Incorrect Counts

[BASHERS33]

Accidentally posted this in the php forum.

Does anyone know a workaround for this?

If I select from table A, join table B, and join table C and within it I use COUNT(B.ID) AS count it does NOT return a count of B rows alone, it returns the number of B rows MULTIPLIED by the number of C rows.

Any way I can get counts to work properly in multiply joined queries?

I thought someone told me that if you only do ONE count it returns correctly no matter how many joins you have, but I guess I misunderstood.

In one place I have a select with FIVE joins and a count is involved there too so this is a huge mess.

All i am getting from the second joined table is an average, I think. But I guess there's now ay to group that separately? lol

I am having yet another issue also. When I use an inner join it keeps saying the field from table A is an unknown column yet if I do a left join it does not say that. This is being run through Invision functions so it could be their function setup having a bug. But the counts is a HUGE issue right now.

Last thing I need to fix before being done with this project for a while and I run into this crap.

Averages work no matter what because it's simply averaging the same things x number of times, but counts are obviously wrong.

[Jordan]

Can you show us your SQL query?

[BASHERS33]

Here is an example, but see it's being passed theough Invision's function, but in case you get enough info from this here is ane xample of what it looks like:

$this->DB->build( array( 'select'    => 'r.mid,r.mem_name,COUNT(r.mid) AS count,AVG(r.overall) AS bias,MAX(r.time) AS latest',
                         'from'    => array( 'table1' => 'r' ),
                         'where'    => "r.locked='0' and r.approved='1'",
						 'add_join' => array(
        															array(
																		'select' => 'AVG(rr.rating) AS rating',
																		'from'   => array( 'table2' => 'rr' ),
																		'where'  => "rr.rev_mem_id=r.mid",
																		'type'   => 'left',
     															  ),
        															array(
																		'select' => 'm.members_display_name,m.member_group_id',
																		'from'   => array( 'members' => 'm' ),
																		'where'  => "m.member_id=r.mid",
																		'type'   => 'left',
     															) ),
     					 'group'    => 'r.mid',
     					 'order'    => 'm.members_display_name ' . $order,
                         'limit'    => array( $start, $perpage ),
                         )        );

For one result there are 2 rows in the result, but there are also 2 rows in the other joined table so it multiplies 2 by 2 and gives 4 for the count.

[WingedPanther]

The count function is not applied to the source table, but to the temporary table that results from the join.

[BASHERS33]

I know, but I'm trying to find a way around it. I knew this happened in the past, but then I forgot about it and how it worked.

I just remember some guy said he found a workaround for me a year or two ago, but I had already changed the area he was helping me with and no longer needed it. So I never knew what his workaround was.

I sure hope I don't have to break them up into multiple queries. In some cases I would go from 1 query to 5 or 6.

[WingedPanther]

I would start by dropping into MySQL and testing queries directly. Build up slowly to get what you need. Group by clauses, sub-selects, etc are all useful tools to get what you want. One of the things to be aware of is that a "clever" SQL statement that saves you queries can result in worse performance than using numerous queries. I did an update with sub-selects a while back that dropped performance down to something like O(n^2) or O(n^3). Doing multiple queries gave me O(n), even though it seemed counter-intuitive.

[BASHERS33]

Yeah i know query count sometimes means nothing. But it justs eems when you can do joins it would be quicker to do so than to eb getting the same info elsewhere.

I don't know what I am going to do temporarily. This is a lot of areas and I need to get it at least "working" in those areas and then optimize later. I considered myself done with this project and now this came up.

As far as sub selects, I just was trying my best to not ened to use anything that isn't supported by other DB drivers since it's for Invision software. I would hate to end up having to ahve it only compatible with mysql, for example. But at the same time I do use mysql and most forum admins use that db also, so I do things with that in mind. I don't even know the differences between the DB drivers to begin with, but was told in the past that a lot of things will cause issues in other db drivers.

I also don't use LOWER() in cases where it would ened to be used in other DB setups, so technically this would have tons of bugs even now other than in mysql, I suppose. I may have to just end up having mysql be required.

[WingedPanther]

MySQL SQL syntax is mostly compatible with other DB types. I've worked with SQL Server, Oracle, and Firebird in addition to MySQL, and they are all about the same for basic queries.

[BASHERS33]

By the way as far as saying the count us for rows in a fake table, I must not know what the temporary table consists of because if you do NOT have more than 1 result in the joins then counts work fine. So that means the fake table does not have an additional row for each join.

Correct me if I'm wrong, but the way it seems to me is if you KNOW FOR SURE that between the main table group by and ALL joins if there is only ONE result for all but one of them then counts work correctly.

For instance if I have it grouped by a unique id for the main table, one join always has one result, and the second join can have multiple results then the count is always going to return properly since only one table has more than 1 result. In other words it appears to me it is multiplying them be number of rows in the other tables, not adding them.

However if the main table were grouped by something that can give the group by more than 1 row AND a join has more than 1 row it will give bad results.

So I could have 100 joins and still get the proper count if 99 of them always give 1 result.

So assuming I understand it, I only found 4 places where I need to change things. Everywhere else has only 1 row from each join being tied to it.

edit: it may even be working properly if the group by has more than 1. Because I see a proper result on one page where it's grouped by a member id in a friends table. And I doubt this guy's id only comes up once. I'll check later to be sure.

[BASHERS33]

The following query works. I guess the reason the count in this works is because although there are multiple matches in the ORIGINAL where clause... that group by is for a field which would only come up once. This particular query is for a list of info for someone's friends. See so each friend will only be there once so I guess the count would NOT work if I grouped by friends_member_id, but it does for the other field?

I sure hope I understand all of this as it's a tad confusing when it's going to mess up. Apparently it only messes up like I said if there are MORE than one result of multiple rows?

$this->DB->build( array( 'select'    => 'p.friends_friend_id',
                         'from'    => array( 'profile_friends' => 'p' ),
                         'where'    => "p.friends_member_id='{$this->memberData['member_id']}' and p.friends_approved='1'",
						 'add_join' => array(
        															array(
																		'select' => 'COUNT(r.id) AS count,MAX(r.edit_time) AS edit_time,MAX(r.time) AS time',
																		'from'   => array( 'test' => 'r' ),
																		'where'  => "r.mid=p.friends_friend_id and r.locked='0' and r.approved='1'",
																		'type'   => 'left',
     															  ),
        															array(
																		'select' => 'm.members_display_name,m.member_group_id',
																		'from'   => array( 'members' => 'm' ),
																		'where'  => "m.member_id=p.friends_friend_id",
																		'type'   => 'left',
     															) ),
     					 'group'    => 'p.friends_friend_id',
     					 'order'    => 'm.members_display_name ASC',
                         'limit'    => array( $start, $perpage ),
                         )        );