[RESOLVED] MAX Sub Query

[Tezread]

I have a perfectly working LEFT query but when i try to add a sub query it is not having it:

here is the working query

SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, supportactivityuser.username AS username, supportactivityuser.timeentered AS timeentered, qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id FROM supportactivity LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name WHERE supportactivityuser.username = 'fudge'  ORDER BY supportactivity

and here is the query + the sub query. Im trying to select the MAX timeentered

SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, supportactivityuser.username AS username, supportactivityuser.timeentered AS timeentered, qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id FROM supportactivity LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name WHERE supportactivityuser.username = 'fudge' AND timeentered = (select MAX(timeentered) FROM supportactivityuser) ORDER BY supportactivity

I have searched the archives and cannot see where im going wrong here

[cgraz]

Can you be more specific with what's not working? Are you getting an error message? Is it not returning any rows?

I'm not familiar with subqueries, but I would recommend the following:

Break down your query into multiple parts, trying each separately. I would first try the subquery by itself, then when you get the result, manually insert it into the main query and see what you get. Also take a look at your DB records to see if that should be returning anything. If you need to, remove parts of the query that are "extras" such as the ORDER BY clause.

Also, do you need a GROUP BY on your MAX() function?

should "AND timeentered = (select..." be supportactivity.timeentered? Same with your ORDER BY clause. Be sure to specify the tablename.fieldname.

And an unrelated note: You may want to consider using aliases for your table names to make your query more readable when you're calling fields using table.fieldname

[Piranha]

Do you get an error message? with mysql_query($sql) or die(echo mysql_error())

Edit: What result do you want? One row with the last insert into supportactivityuser or what?

[Tezread]

the error message is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /disk1/home3/mindseye/public_html/notts_quality/info_resource/sourcesofhelp_page2.php on line 88

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /disk1/home3/mindseye/public_html/notts_quality/info_resource/sourcesofhelp_page2.php on line 116 

the query does work as I have broken it up and tested it incrementally

[Piranha]

That is PHP errors that are not directly related to the query but to the result of the query. Don't you get any error from MySQL? How does the PHP code look like?

[Tezread]

In phpmyadmin:

Error

SQL-query :

SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name
WHERE supportactivityuser.username = 'fudge' AND timeentered = (
select MAX( timeentered )
FROM supportactivityuser )
ORDER BY supportactivity

MySQL said:

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 'select MAX(timeentered) FROM supportactivityuser) ORDER BY supp
Back

[Piranha]

try ...AND timeentered IN (SELECT...

[Tezread]

To be honest I have looked at DISTINCT in the manual but i wasn't sure how how to incorporate it into my current db query

[Tezread]

Error

SQL-query :

SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name
WHERE supportactivityuser.username = 'fudge' AND timeentered
IN (

select MAX( timeentered )
FROM supportactivityuser
)
ORDER BY supportactivity

MySQL said:

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 'select MAX(timeentered) FROM supportactivityuser) ORDER BY supp
Back

[Piranha]

What version of MySQL do you use?

[Tezread]

tried this too

SELECT MAX( supportactivityuser.timeentered ) AS timeentered, supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, supportactivityuser.username AS username, qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id
FROM supportactivity
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name
WHERE supportactivityuser.username = 'fudge'

MySQL said:

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
Back

[Tezread]

Piranha wrote:

What version of MySQL do you use?

Version 4.0.21

[Piranha]

What result do you want? One row with the last insert into supportactivityuser or what?

[NogDog]

Tezread wrote:

Version 4.0.21

Sub-queries requires MySQL 4.1 or later.

[Tezread]

Piranha wrote:

What result do you want? One row with the last insert into supportactivityuser or what?

All the rows that contain the last timeentered

[Piranha]

NogDog wrote:

Sub-queries requires MySQL 4.1 or later.

Ty NogDog, I thought it was 4.0.

Tezread, you basically got your answer here. It may be possible without subqueries, but I don't know any way. I suggest that you make sure the database gets updated to avoid such problems in the future.

[Tezread]

My host will not upgrade to 4.1. I think i will be leaving them as I have had a situation before where I could have used INSERT... ON DUPLICATE UPDATE but again this is restricted to version 4.1+

Thanks for clarification and assistance everyone

[Piranha]

I would surley leave them if they won't upgrade. It is their job to see to it that you have the programs that you need, that's why you pay them.