Hi,

Sorry if I haven't explained this problem very well, but any help/suggestions would be great

If you need to get data from different tables and perform two separate count queries of records from another table is it possible to do it all in one query or do multiple queries need to be used?

I have one query that gets data from two tables (and the mapping table), then I get the results using a while loop which also prints out the table, within this while loop (and in the table) I want to output the total number of records in a spearate table that is not used in the orignial query that is used to output the results in the while loop (but it is joined by a mapping table:

1) Should a separate query be used?
2) Should it be used within the while loop?

Thanks,

Select Count(*) From Tables Where Expression

You would run one of those queries for every count you need.

Why are you using a loop?

Learning Newbie, I don't think I explained it correctly so I will try and explain it better. I have the following tables:

PHP Code:

tbl action
id
actionId
description
 
tbl event
id
eventId
description
 
tbl eventAction
id
eventId
actionId
 
tbl people
 
tbl organisation
 
tbl organisationPeople 


I currently have a query that gets events:

PHP Code:

$eventIds =& $dbcrm->query("SELECT * FROM events E, people P, peopleEvent PE, organisationPeople OP WHERE P.personId = PE.personId AND E.eventId = PE.eventId AND P.personId = OP.personId AND OP.organisationId = '$_GET[id]' ORDER BY E.date DESC");
checkDBError($eventIds);
 
while { // keep getting rows until there are no more rows to get
} 


The while loop is beling used to get all of the data and print out event records for people stored in the people table.

(An event can have multiple corresponding actions)

In the results printed out from the while loop I also want to include the number of actions that correspond to that event record: which means I need to use a COUNT query so I can display the number of actions for the event which means I need to query the eventAction table as well.

Therefore, my question is (sorry for the long post) since I am using a query already (with while loop) to get event records, where do I put the count query? Inside the while loop? for example:

QUERY1 (get events from tbl event, tbl people and tbl peopleEvent where organisation = $_GET[id])

while { print out records
I also want to display the number of actions that correspond to these events that have been retrieved from QUERY1
}

Where do I put the query (QUERY2) that COUNTS the number of actions for the events listed in the above while loop? The tables required for the COUNT query are tbl event, tbl action and tbl eventAction.

Thanks,

You should (or would benefit to) use a join in your query rather than a while loop. PHP is extremely slow. When you run a bit of code, there's a part of the web server called an interpreter that parses your text code, and runs it. Even MySQL is compiled, which means the code is already parsed and translated into machine language. Then there are massive inefficiencies wherever there's interprocess communication, so a round trip to the database is a hugely expensive thing. It can work fine when you have 20 rows in your table, and start timing out at a few hundred.

From what I can tell, the way you're using PHP to run a while loop over results coming out of the database, is reinventing the wheel. The database is already highly specialized at what you'd like to do. It's not only faster, but much easier.

Select A.ActionID, E.EventID, Count(*) From Action A Join ActionEvent AE On A.ActionID = AE.ActionID Join Event E On AE.EventID = E.EventID Group By A.ActionID, E.EventID

That will give you only the counts, and the IDs they correspond to. You could instead ask for all the data. This query will parse, execute, and stream the results back faster than the first 2 or 3 times a loop would run with a similar Select Count(*) Where X = ? query nested inside. It will put a substantially lower burden on your server. And it won't force you to write looping constructs, at least here.

If you want to get all the information from one of the tables, instead of just a count, use a sort instead of a grouping. That will keep all the records together that belong in a group, but without collapsing them down to an aggregate like a sum, average, or count.

Excellent comments once again Learning Newbie, thanks for your help. I changed the query and was able to use it to get the number of actions for specifc events based on different criteria.

I was wondering, if you want to get three different counts from the same table would you include it in one query rather than use three separate queries?

For example,

Where complete = 1, 2 and 3 would you need three separate queries to get the COUNT for each of them?

PHP Code:

$actionNum =& $dbcrm->getOne("Select COUNT(*) From eventAction EA Join action A On EA.actionId = A.actionId Join events E On EA.eventId = E.eventId WHERE EA.eventId = ".$eventId['eventId']." AND A.complete = '1' Group By E.eventId"); 


If I wanted the # of rows in a single table that have the given values in a particular field, I'd write a query like this

Select Complete, Count(*) As NumberOfRecords
From Table
Where Complete In (1, 2, 3)
Group By Complete

Which will return

Complete               NumberOfRecords
1                          132
2                          891
3                          17

Then, my application (be it PHP, ASP, ASP.NET, etc) has all the information it needs, but it's only sent 1 query to the database, and it's only had to wait on the results of 1 query instead of 3. That makes it run faster, it lets other things going on on the server run faster, because the database is less tied up. And most of all, it makes it easier for you to write.

That's a much more efficient way of getting the COUNT than using two separate queries. Is there any way to assign the totals for each (1,2,3) to a field name that you can then use?

for example, something like this:

PHP Code:

$query = "SELECT complete, COUNT(*) AS numRecords1, numRecords2, numRecords3 FROM table
WHERE complete IN (1, 2, 3)
GROUP BY complete"
 
mysql_fetch_array etc
 
echo $query['numRecords1'];
echo $query['numRecords2'];
echo $query['numRecords3']; 


Possibly with a pivot, but I'd be shocked if MySQL supports those. I suppose you could possibly write out the results of the query above into a temp table and then use a series of sub queries to format all the results onto the same row. I think getting the right value into the right column, predictably and reliably, will be a lot more difficult than walking 3 rows in the result set, tho. Especially when they're so well correlated - here's the ID value, here's the count that corresponds to it.

I think you mentioned referencial integrity in a different post in answer to a question I asked about how does information get updated in the mapping table to match corresponding records. Am I right in thinking that because foreign keys are defined in the mapping table they are automatically updated when data is inserted into the tables? For some reason I'm finding it difficult to understand this lol

If I have a three tables called organisation, people and organisationPeople, and there is already existing data about organisations stored in the organisations table and I want to insert new people into the people table and each person belongs to an organisation.

Is this correct: There is no personId field in the organisation table and no organisationId in the peope table because these are stored as foreign keys in the mapping table?

If I insert a new person into the people table how do I match that record with an organisation? Because to match it up you need the personId and if that's auto increment in the people table how do you insert the peopleId and the organisationId in the mapping table to show that person record corresponds to that organisation?

Am I right in thinking that because foreign keys are defined in the mapping table they are automatically updated when data is inserted into the tables?

It depends... It depends upon you say that you want to CASCADE or RESTRICT your updates.

You can specify to the DB that you want to cascade the updates.
In that case, if you update a field that is used as a foreign key, the depending field would be updated too.
Everything would be done in a transaction, meaning that if one of the sub-tables update fails, everything is rollbacked to the original state.

On contrario, if you don't cascade updates, the update will fail, because the sub-table integrity would not be kept after the update.

http://www.oreillynet.com/onlamp/blo...t_foreign.html
http://www.gunduz.org/seminer/pg/fkey.php
No, the update will be refused, because it would

Ok, so in terms of the tables I have:

PHP Code:

tbl organisation
organisationId
name
 
tbl organisationPeople
id
personId (FK)
organisationId (FK)
 
tbl people
personId
firstname
surname 


When I insert a new person into the people table, where do I specify what organisation they belong to? I can't insert it into the mapping table and if I inserted it into the people table there would be no point in having the organisationId in the mapping table? Unless I do include an organisationId field in the people table and make both of the FK's in the mapping table the foreign keys for the people table?

It depends on what constraints you want to enforce.
If you want every users to be incorporated into an organisation, then yes, I would move the organisation into the peoples table, like that (in postgresql):

create table organistations(
  organisation_id serial,
  name varchar(200) not null,
  contraint primary key (organistation_id)
);

create table peoples(
  people_id serial,
  firstname varchar(50) not null,
  surname  varchar(50) not null,
  lastname varchar(50) not null,
  organisation_fk integer not null references organisations(organisation_id),
  constraint primary key (people_id)
);

This will ensure that any user you create is attached to an existing organisation.
But it limits you on a 1 to 1 relation between a user and an organisation (meaning that a user MUST be part of an organisation).

If you want a person not to be referenced to an organisation, either drop the foreign key, or use a reference table:

create table organistations(
  organisation_id serial,
  name varchar(200) not null,
  contraint primary key (organistation_id)
);

create table peoples(
  people_id serial,
  firstname varchar(50) not null,
  surname  varchar(50) not null,
  lastname varchar(50) not null,
  constraint primary key (people_id)
);

create table organisations_peoples(
  people_fk integer not null references peoples(people_id),
  organisation_fk integer not null references organisations(organisation_id),
  constraint primary key (people_fk, organisation_fk)
);

That way, you can have a user not being in an organisation, but you can keep a foreign key constraint for those who are.
Note that the reference table have a multiple PK. This means that you still have a 1 to 1 relation between the peoples and the organisations.
Change it to

constraint primary key (people_fk)

To have a 1 to many relation between a user and organisations. That way, you could have several rows in the reference table who are linking 1 user to several organisations.

Thanks for taking the time to explain it in detail I think I will use a mapping table and not include organisationId in the people table.

But how do you macth the records in the mapping table when a new person is inserted?

for example,

if you insert a new person (when user has entered details into a form and clicked submit)

the person data will be inserted, but where in that insert query would you include the organisationId for the organisation? You can't insert it into the mapping table at the same time because the personId would not have been generated. The only way I can see how to do it is to use two queries, one for inserting the person into the people table and then a different query to get the newly inserted person from the people table and insert the personId and organisationId into the mapping table (organisationPeople)

Am I missing something here?

the person data will be inserted, but where in that insert query would you include the organisationId for the organisation?

You take it the wrong direction...

The foreign key is on the reference table.
That means that it's the datas in the reference table that must validate against peoples table. Not the other way.
You can insert as many peoples as you want, without linking them to any organisations, but you cannot declare a link between a non existing user and an organisation, nor between an existing user and a non existing organisation.

Like you said, you need 2 queries. Or, if you want to automate it, you need either to put a "on insert" trigger on the people table or to use a stored procedure that will take care of executing the 2 statements.

Stretching a bit what may come next, if you have a user in both table, the select is quite simple:

select *
from peoples p, organisations_peoples op
where p.people_id = op.people_fk;

You can write it that way too, I find it more easily readable:

select *
from peoples
inner join organisations_peoples op on p.people_id = op.people_fk;

That type of relation between the 2 tables is called an inner join. Only rows present in both table will be returned.

But, this won't give you peoples that are not associated to any organisations.
To do so, you have to use an outer join.
The outer join define a "base" table, whose every rows must be returned, and an "optionnal" query who will maybe return something...
You write it:

select * 
from peoples p
left outer join organisations_peoples op on p.people_id = op.people_fk;

The "left" outer join signifie that the table on the left of the query (or the previously declared, if you prefer) is the one to be taken as a base.
Note that when you have several joins, you cannot have an inner join after an outer join.
You must organize your query to have every inner joins before every outer joins, like this:

SELECT 
        ab.idsArtBasisID
        ,n.strLangerName
        ,tbm.intSum AS numBestellteMenge
        ,lagb.numBestandMenge
        ,ts.strStatus AS strBestellStatus
        ,ts.blnNichtBereit AS blnNichtBereitZuSenden
        ,talp.numAusloesMenge
        ,talp.numBestellMenge
        ,tvk.numVK
    FROM csaarl
        INNER JOIN dbo.funArtBasis(@intMdtUID) ab ON ab.idsArtBasisID = csaarl.intID
        INNER JOIN dbo.tblArtName n ON n.idsArtNameID = csaarl.intSubID
        INNER JOIN @tmpVK AS tvk ON tvk.idsArtBasisID = csaarl.intID
        LEFT OUTER JOIN dbo.fwtblArtLiefPrio(@intMdtUid) AS talp ON talp.intArtBasisFK = csaarl.intID
            AND talp.intBestellPrio=1
            AND talp.strLiefPrioTypCode='AV'
        LEFT OUTER JOIN dbo.fwtblLagBestand(@intMdtUID) lagb ON lagb.intArtBasisFK = csaarl.intID
        LEFT OUTER JOIN @tmptBestellteMenge tbm ON tbm.idsArtBasisID = ab.idsArtBasisID
        LEFT OUTER JOIN @tmpStatus ts ON ts.idsArtBasisID = ab.idsArtBasisID
    WHERE 1=1 
        AND csaarl.blnIsActive = 1
    ORDER BY csaarl.intRowNumber

This is a query I crafted this afternoon. It calls many user defined functions and stored procedures too, but you got the picture...