more than 2 records with the same ID

crmpicco · January 27th, 2005, 06:46 AM

i have a mysql db.

table name='trip_master'

in there is a column called 'trip_id'

when there are more than 2 records with the same ID i want to display the AMOUNT in a number into a HTML table.

an example of a 'trip_id' is BT/Y5RSWY

any ideas?

Picco

ChrisScott · January 27th, 2005, 06:58 AM

This query will get you the trip_id & total for that trip where there are more than 2 records...

Code:

SELECT trip_id, COUNT(*) AS total FROM trip_master GROUP BY trip_id HAVING total > 2;

Cheers,

Chris

crmpicco · January 27th, 2005, 07:06 AM

SELECT trip_id, COUNT(*) AS total FROM trip_master GROUP BY trip_id HAVING total > 2;

Can that work in ASP?

crmpicco · January 27th, 2005, 07:07 AM

My code is sitting like this:

set rs4=con.execute("SELECT trip_id, COUNT(*) AS total FROM trip_master GROUP BY trip_id HAVING total > 2;")

what do you reckon.

BTW, thanks for your help mate.

Picco

crmpicco · January 27th, 2005, 07:09 AM

sorry. the table name isnt trip_master. Its trip_details

ChrisScott · January 27th, 2005, 07:21 AM

Code:

set rs4=con.execute("SELECT trip_id, COUNT(*) AS total FROM trip_details GROUP BY trip_id HAVING total > 2;", , adCmdText)

Should be fine.

Cheers,

Chris

crmpicco · January 27th, 2005, 07:27 AM

whats adCmdText?

ChrisScott · January 27th, 2005, 07:35 AM

It specifies that you are executing an SQL statement.
The default value is adCmdUnknown and means that ADO has to work out what you are trying to do before passing the command on to the db (this slows things down).

Best regards,

Chris