MySql GROUP BY problem - need help

[eatc7402]

I am using a MySql Server version 4.0.21.

 

I have 3 related tables. Table 1 (extra_alarm_fires e),  Table 2

(extra_alarm_responces er), and Table 3 (companies_table c).

 

They contain information about major fires. Table 1 has data such as

date, time,  & address of a particular fire. Table 2 contains the responces

to that PARTICULAR fire such the First Alarm, Second Alarm, Third

Alarm and the times for each alarm. Table 1 has a one-to-many relationship

to Table 2, and has a relating primary key (fires_to_responces_link2).

Table 3 holds information about the actual fire companies that responded

to each item in Table 2, such as Engine 1, Ladder 6, responded on the

Second Alarm to some fire. Table 2 and Table 3 have a one-to-one

relationship linked by key (responce_to_companies_link).

 

I have the following SQL query which SEEMS to work fine:

 

SELECT * FROM eatc7402.extra_alarm_fires e,

                  eatc7402.extra_alarm_responces er,

                  eatc7402.companies_table c

WHERE        e.fires_to_responces_link2 = er.fires_to_responces_link2

AND            er.responce_to_companies_link=c.responce_to_companies_link

ORDER BY    e.city_name,

                  e.fire_year DESC ,

                  e.extra_alarms_sort_order,

                  er.sort_order_responces

 

This query returns all the rows for each fire as I would expect.

 

However, if I add this line,

 

SELECT * FROM eatc7402.extra_alarm_fires e,

              eatc7402.extra_alarm_responces er,

              eatc7402.companies_table c

WHERE    e.fires_to_responces_link2 = er.fires_to_responces_link2

AND        er.responce_to_companies_link=c.responce_to_companies_link

AND        c.engines like '%11%'

ORDER BY e.city_name,

              e.fire_year DESC ,

              e.extra_alarms_sort_order,

              er.sort_order_responces

 

Which returns EACH record that Engine 11 responded to. However each

record in this case is a SUBSET of a particular fires records. In other words I

receive only a subset of the records related by the primary keys.

 

So, I figured some kind of  a  GROUP BY clause might help out. So I

tried the following:

 

SELECT * FROM eatc7402.extra_alarm_fires e,

              eatc7402.extra_alarm_responces er,

              eatc7402.companies_table c

              GROUP BY (e.fires_to_responces_link2)

WHERE    e.fires_to_responces_link2 = er.fires_to_responces_link2

AND        er.responce_to_companies_link=c.responce_to_companies_link

AND        c.engines like '11%'

ORDER BY  e.city_name,

                e.fire_year DESC ,

                e.extra_alarms_sort_order,

                er.sort_order_responces

 

But instead of working I get the following error.

 

You have an error in your SQL syntax; check the manual that corresponds to your

MySQL server version for the right syntax to use near 'WHERE       

e.fires_to_responces_link2 = er.fires_to_responces_link2  AND  ' at line 5

 

I am ATTEMPTING to return ALL rows that contain the same primary key value

as the row returned by the ?subselection? for fires to which Engine 11 responed

to (example 2). I don?t see my error, and am NOT SURE if this even the right

way to accomplish what I desire to accomplish.

 

Any help would be appreciated.

 

eatc7402

 

[artacus]

Even though MySQL will 'sort of' let you, don't join your tables like that... it loose aliases for some reason, and you end up scratching your head... pretty much like you're doing.

 

SELECT * 
FROM eatc7402.extra_alarm_fires AS e 
JOIN eatc7402.extra_alarm_responces  AS er ON e.fires_to_responces_link2 = er.fires_to_responces_link2
JOIN eatc7402.companies_table AS c ON  er.responce_to_companies_link = c.responce_to_companies_link
WHERE  c.engines like '%11%'
ORDER BY e.city_name, e.fire_year DESC ,
e.extra_alarms_sort_order, er.sort_order_responces

 

PS response is spelled wrong

[eatc7402]

Well, I now have this:

 

SELECT * FROM eatc7402.extra_alarm_fires AS e

        JOIN eatc7402.extra_alarm_responces AS er

        ON e.fires_to_responces_link2 = er.fires_to_responces_link2

        JOIN eatc7402.companies_table AS c

        ON  er.responce_to_companies_link = c.responce_to_companies_link

WHERE        c.engines like '%11%'

ORDER BY      e.city_name,

              e.fire_year DESC ,

              e.extra_alarms_sort_order,

              er.sort_order_responces

 

Which works with the JOIN lines adjusted as suggested.

(BTW  the my first join syntax was simply the way

MySql Query EDITOR formultated the query).

 

Then I added back this:

 

SELECT * FROM eatc7402.extra_alarm_fires AS e

        JOIN eatc7402.extra_alarm_responces AS er

        ON e.fires_to_responces_link2 = er.fires_to_responces_link2

        JOIN eatc7402.companies_table AS c

        ON  er.responce_to_companies_link = c.responce_to_companies_link2

GROUP By      (e.fires_to_responces_link2)

WHERE        c.engines like '%11%'

ORDER BY      e.city_name,

              e.fire_year DESC ,

              e.extra_alarms_sort_order,

              er.sort_order_responces

 

and I am back to my original problem.

The error near the GROUP BY clause still DOES NOT WORK.

While appreciate the comments about the JOINS, my bigger

concern is the GROUP BY issue.

 

eatc7402

 

[fenway]

You can't have a group by there.

[eatc7402]

Yes, you are correct. A careful reading of the MySql sql syntax indicates the the

GROUP BY clause must come after the WHERE  clause like this:

 

SELECT * FROM eatc7402.extra_alarm_fires AS e

            JOIN eatc7402.extra_alarm_responces AS er

            ON e.fires_to_responces_link2 = er.fires_to_responces_link2

            JOIN eatc7402.companies_table AS c

            ON  er.responce_to_companies_link = c.responce_to_companies_link

WHERE      c.engines like '%11%'

GROUP BY  e.fires_to_responces_link2

ORDER BY  e.city_name,

                e.fire_year DESC ,

                e.extra_alarms_sort_order,

                er.sort_order_responces

 

Which fixed my SYNTAX ERROR, but NOT the FUNCTIONAL ERROR.

In other words, the same query with or whithout the

GROUP BY clause returns the exact same results.

 

My desire is to have the searches on, lets say, Engine

value, which in a SUBTABLE, will return ALL  ROWS that correspond to

the primary key from the master table returned by the 'subsearch'..

If I am not being clear on what I desire pleasec let me know and I

will try a more explicit example.

 

eatc7402

 

[fenway]

I'm not sure I understand what you're trying to achieve.

[eatc7402]

Okay, lets try this...

 

If I do a query that asks, "Which major fires did Engine 11 respond to in 2005"

I get 32 rows returned. Each returned row contains the primary key of

the main table, lets a number like, 200.

 

A query for a FIRE returns full relational results like this....

This means the a major fire has a GROUP of records with the key of '200',

because that major fire also has OTHER ALARMS, but engine 11 only

responded on ONE of those alarms.... like this

 

Pri_key    Address          Date            Alarm        Time  Engines    Ladders

------------------------------------------------------------------------------------------

200        123 Some St.  2/11/2005    1st Alarm    1205  6-14-21    6-9

200        123 Some St.  2/11/2005    2nd Alarm    1209  19-11      3

200        123 Some St.  2/11/2005    3rd Alarm    1213  8-7          4

ect, ect.

 

A search for the FIRE will return the above, ALL the related records for

one fire EVENT.

 

But a search for Engine 11's responses will only return ONE ROW,

with the primary key indicated, like this...

200        123 Some St.  2/11/2005    2nd Alarm  1209  19-11      3

 

Now using PHP I could first do a query to gather subinformation,

such as ANY records with primary keys,  and THEN do a second query

with the search keys set to the primary keys discovered in the first query.

However, this could be be a LARGE query depending on how many keys

were discovered by the first query. And it seems like an inefficient way to

perform the  task.

 

So, not being an SQL expert I thought maybe there was a way in the

first query to specify it to return ALL ROWS where the pirmary key

are equal to any rows returned by the subquery for an ENGINE lets say

which comes from a subtable, and seems to scoot around the relation

aspect of the date.

 

It seems I am trying to discover a way to do a 'RELATIONAL QUERY'

even though I am search for data in a subtable of related tables.

 

Hope that explains it better.

 

eatc7402

 

 

[artacus]

Hope that explains it better.

 

Not at all. But just change your group by column to get the info you are looking for.

 

I think you are wanting to group by the primary key of table e.

[eatc7402]

Well.... After more thought I think what I really need is not a GROUP BY,

but an EXPAND BY.... which of course does exist, at least not exactly.

Your suggestion about 'changing' the GROUP BY field to gather the

primary keys is true, and that is exactly what it is doing now. Which is why

the query runs the SAME with or without the GROUP BY.

 

What I really need is a SUBQUERY to gather a 'iist' of ALL the desired,

and then run the master query looking for the master keys.

 

But unfortunately the MySql Server version is 4.0.21, not 4.1 or

later, as as such does NOT support subqueries.

 

My alternate strategy is use PHP and first query to determine the

list of desired primary keys, and populate an array with those values.

Then, run a second query (in a loop) using the list of primary key as

the WHERE, thus returning all the desired rows.

 

That seems to me to be a very inefficient way to do it, but it

may be the only way when subqueries are not available.

 

If someone has an alternate method, I would love to hear it.

 

eatc7402

[eatc7402]

The MySql Reference Manual suggests a way for those servers PRIOR to  Version 4.1

, and thus not supporting subquereis, there may be a way to rewrite the jois, and the sql

syntax to accomplish the desired result.

 

However, not being an SQL guru, could anyone suggest another way to write

a query that would gather ALL the desired primary keys and do a search on them,

without the several pages of PHP code required to accomplish the same result.

 

eatc7402

[artacus]

You don't need a subquery here. You just need to change your group by like I said.

[eatc7402]

Well I don't think so as that is exactly what the GROUP BY is now

gathering, the primary key of table 'e'. Yes those are the keys for the

records I desire.... but I then need to run another query, using those

keys as the WHERE's., and that is exactly what I am attempting to avoid.

 

The clause says...

 

GROUP BY (e.fires_to_responces_link2)

 

and e.fires_to_responces_link2 is the PRIMARY key from Table 1 (the main

table) to Table 2, the responces table (which I misspelled, sorry).

It gathers today 32 rows. However there over 100 keys not being selected

by the query as it is written.

 

eatc7402

 

 

[shoz]

It's usually best to give an explanation similar to the following to be clear. Note that I make no reference as to the kind of query or the syntax of the query I believe is needed.

 

eg:

This is the create statement for the tables.

 

SHOW CREATE TABLE tablename

 

This is an example of the info in each table.

 

table1
---------------------------------------------------
column1name | column2name |
table1's example info

table2
---------------------------------------------------
column1name | column2name |
table2's example info

etc.

 

This is what I want from the database if given etc. In your example if would be "if given the table1.enginenumber. For instance 11"

 

example output

 

Explanation of how the tables are related if you believe it's not clear from the examples.

 

I was originally going to offer a solution in the form of a single query but I don't believe I have enough information as to how the tables are related.

 

Based on what you've said you won't need to loop through the results. Once you do the first query and know the primary keys for the fires you can write another query that will have a WHERE clause similar to

WHERE firetable.primarykey IN (200, 300, 400)

 

Where 200,300 and 400 are the ids for the fires retrieved from the first query.

 

If you decide that you'd like to explain what you're looking for again, then remember to only give the information about the tables in the manner I describe. No reference to types, or the syntax of queries.

[eatc7402]

Okay, first thanks for the suggestion about using the special formmat

for code sections.

 

Yes, I just tried the

 

WHERE 'primary_key' IN ('20', '68', '40') 

 

and that works nicely and 'possibly' removes the need for looping.

But I wonder how large the IN clause can be. There may be cases where

there may be 10,000 primary keys to search. The data we are searching

are all the major fires in Minneapolis, and St. Paul from 1911 to 2007

(today). There is a lot of data. A hundred (or more) major fires each year

for nearly a hundred years, for each city, presents a lot of keys. Thus my

question on how many items can be fit into one IN clause.

 

The keys may be need to be broken into subsets, and several

queries with IN clauses run, if there is a limit as to how many

entries arec allowed. ??

 

eatc7402

 

p.s. I will provide another message describing how the table are related

again using the 'code' styler. But it's late tonite here....

 

[artacus]

You don't want to use IN with 10,000 arguments.

 

I'm not sure exactly what you need to group by. Look at your ungrouped data and see what has the same values where you'd like to reduce it to one row. I would think you would want each unique fire event.

[shoz]

p.s. I will provide another message describing how the table are related

again using the 'code' styler. But it's late tonite here....

 

Keep in mind that the code tags (

) are only for formatting. What I'm saying is that if you decide to explain what you're looking for give the information that I've asked you to provide. The formatting is secondary but should also be included

 

and that works nicely and 'possibly' removes the need for looping.

But I wonder how large the IN clause can be. There may be cases where

there may be 10,000 primary keys to search. The data we are searching

are all the major fires in Minneapolis, and St. Paul from 1911 to 2007

(today). There is a lot of data. A hundred (or more) major fires each year

for nearly a hundred years, for each city, presents a lot of keys. Thus my

question on how many items can be fit into one IN clause.

 

If this is to be a paginated listing you can ORDER the results by the date of the fires in the first query and go through the results in groups.

 

eg:

SELECT
...
WHERE ...
ORDER BY
tablename.datefield
LIMIT
0,50

 

LIMIT 50, 50
LIMIT 100,50
etc...

 

If you need to retrieve all the information quickly then you can pass all the ids to the second query or perhaps more prudently send them in batches of 500 after sorting them. To see how MYSQL would handle it, I did try this myself and it does work as expected if given 10000 ids.

 

As far as how long the string can be. The max_allowed_packet variable I believe is the server var that most greatly impacts what the maximum length that a string sent to the server can be. As stated in your other topic however, it's unlikely that you'll hit the default figure.

 

How is this script going to be used btw?

[eatc7402]

Thats why in a previous reply I stated that I relized that GROUP BY 'reduces'

the query to ONE ROW per query. That is what I have already (before using

a GROUP BY) have. What I need is an..

 

EXPAND BY

 

but such a construct does not exist, exactly. That is where the discussion about

sub queries on primary keys ensued. As I have mentioned my version

of MySql does not support sub queries. I need a two step process.

Step 1. run a query to gather the subset of required primary keys.

Step 2. run a second query using the list of primary keys gathered in step one

          as the selectors in the WHERE clause

 

That will return ALL the rows of the main table, AND the subtable with

ALL the rows that 'belong' to a single fire incident.

 

as in...(This is what I WANT)....

 

Pri_key    Address          Date             Alarm         Time   Engines    Ladders
------------------------------------------------------------------------------------------
200         123 Some St.  2/11/2005     1st Alarm     1205   6-14-21    6-9
200         123 Some St.  2/11/2005     2nd Alarm    1209   19-11       3
200         123 Some St.  2/11/2005     3rd Alarm     1213   8-7          4

 

 

This is what I am getting....

 

200         123 Some St.  2/11/2005     2nd Alarm    1209   19-11       3

 

Because the search for a fire that Engine 11 responded to is kept

in a SUBTABLE (on the MANY side of the join), and hence

returns only one of the three rows that  pertain to this one fire.

 

So, I then need to take the primary kee returned (then 200 key), and

do another query with it as the seector in the WHERE

clause to return ALL THREE ROWS that actually have the 200 value

as their key.

 

Hence the need for not a GROUP BY, but rather the equivalent

of an EXPAND BY.  Therefor the suggestion of using a

 

WHERE main_table.primary_key IN('a  list of keys')

 

Where the list of keys gets returned by Step 1 (above).

 

I hope that explains it.

 

I have two tables A main table (with primary keys)

and a subtable joined to the main table in a

ONE (the main table) to MANY (the subtable).

 

However searches for fields located in the subtable return

a subset of the rows that actually contain the same primary key,

because I seem to be not findinf these fields by going through

the full relationship created by the one-to-many join.

 

 

eatc7402

[artacus]

Ok. I got ya!

 

So as I understand it, you want to look at every company that responded to every fire that a certain company responded to.

 

You can do this w/o subqueries by joining tables c and er twice.

So you'll start out w/ c as your primary table and join up to e so you have a list of every fire they  responded to. Then you use that to join back down to c2 so you have all of the companies that responded to that fire.

SELECT e.*, er2.*, c2.* 
FROM companies_table AS c
JOIN extra_alarm_responces  AS er 
     ON  er.responce_to_companies_link = c.responce_to_companies_link
JOIN extra_alarm_fires AS e 
     ON e.fires_to_responces_link2 = er.fires_to_responces_link2
JOIN extra_alarm_responces  AS er2 
     ON e.fires_to_responces_link2 = er2.fires_to_responces_link2
JOIN companies_table AS c2 
     ON er2.responce_to_companies_link = c2.responce_to_companies_link
WHERE  c.engines like '%11%'
ORDER BY e.city_name, e.fire_year DESC ,
e.extra_alarms_sort_order, er.sort_order_responces

[btherl]

Does this summarize what you want?

 

"All records for each fire which engine 11 responded to".

 

Algorithmically:

 

1.  Fetch fire identifier (PK) for each fire responded to by engine 11

2.  Fetch all records for each fire from step 1

 

Normally it would be done using a subquery.  Instead, you can use a temporary table.  Someone more familiar with mysql could give you the details, but I imagine you would create the temporary table containing PKs of all fires responded to by engine 11, and then join with that temp table to get your final list of records.

 

Or, you could join once again with your data tables with different aliases to get the data you want.  In that second join, you would NOT apply the condition on engines.  As a result, you would get all rows you wanted from that second join.  It seems messier than the temp table approach, but logical.

 

Essentially, the first joins would restrict the data to engine 11.  Then the second join with the same tables would do the "expand by" that you want, as you are joining on the set of restricted PKs, but without the engine restriction applied to the second join of those table.

 

As for writing the actual query, I need to see your table definitions.  The query is mindbending enough without them..

 

Aha, artacus did it while I was busy typing   Nice job.

[artacus]

Aha, artacus did it while I was busy typing   Nice job.

 

LOL. Great minds think alike.

 

Don't you hate it when you spend 10 minutes crafting the perfect answer and when you go to post you get the little warning saying [red]"You've just wasted the last 10 minutes of your life because while you were typing, someone else already answered the question. Do you want to throw all your hard work away or post it anyhow and look stupid?"[/red]

Well, duh, I want to post it anyhow. LOL

[fenway]

Nicely done... I would have had a hard time trying to guess what the poster was actually looking for.

[eatc7402]

To aritcus...

 

Nicely done... That works fine (I tested it), when the users search is

from the BOTTOM of the hierarchy upward. But when the user might search on

a field in a TOP DOWN  manner they will get multiple rows returned

for each primary key. Bottom up, lets you get three rows for a fire

when searching by Engine number. But searching the same fire by address

(which is TOP DOWN) it returns NINE rows using the query articus suggested.

 

My users will be able to choose from ANY field, at any level,

and mix them to search by. I have to return all the rows for

an individual fire, no matter what field, or what level, the

user chooses to search by.

 

Would I get anywhere to create a FLAT table of all data where

most all rows would contain the top level data (including pri keys), and

those same rows would be partially filled by those fields

coming from lower level tables?

 

eatc702

 

[artacus]

Nicely done... I would have had a hard time trying to guess what the poster was actually looking for.

Thanks... it only took me 12 posts to get to what he wanted

 

Would I get anywhere to create a FLAT table of all data where

No don't do that.

In that case you have 3 options.

 

1) An if statement that will run one of 3 queries. If they are searching on company, you'll use the query above. If on er another query that starts there instead of on c. And if on e, then you don't have to join those other tables at all.

 

2) Upgrade mysql so you can run subqueries

 

3) Use a temp table instead of a subquery.

 

[eatc7402]

Yup, I thought about number 1

 

1) An if statement that will run one of 3 queries. If they are searching on company, you'll use the query above. If on er another query that starts there instead of on c. And if on e, then you don't have to join those other tables at all.

 

I'll have to figure out which field (or fields) the user desires, and run

the appropriate query based on that. I can do that in my php

script beforehand.

 

It seems to me that all this would almost be a 'non-issue' if I

could use a subquery selecting primary keys, so suggestion

number 2, upgrading to a newer version of MySql would be a better

choice. Unfortunately that is not up to me, as the server is provided

by our website ASP. But I can always try to convince them.

 

eatc7402