users who favourited this also favourited this

ok, ill put it in
cheers

You forgot the group by clause

hi, following your advice i have pretty much got this up and running, the only thing is, that when i try and run the count (bandname_art) as favourite in the query i get an error message

so

<!---similar artists--->
<cfquery name="sim_art" datasource="#APPLICATION.DataSource#">
SELECT bandname_art
FROM artist_art
LEFT OUTER JOIN artists_fav
ON artist_art.id_art = artists_fav.id_artkey_fav
WHERE id_usr_fav IN
(select id_usr_fav
from artists_fav
where id_usr_fav <> #session.auth.idnumber#
AND id_artkey_fav = #url.id_art#)
AND id_art <>#url.id_art#
</cfquery>

is fine

but

<!---similar artists--->
<cfquery name="sim_art" datasource="#APPLICATION.DataSource#">
SELECT bandname_art, count (bandname_art) as favourite_count
FROM artist_art
LEFT OUTER JOIN artists_fav
ON artist_art.id_art = artists_fav.id_artkey_fav
WHERE id_usr_fav IN
(select id_usr_fav
from artists_fav
where id_usr_fav <> #session.auth.idnumber#
AND id_artkey_fav = #url.id_art#)
AND id_art <>#url.id_art#
</cfquery>
returns the error message of "You tried to execute a query that does not include the specified expression 'bandname_art' as part of an aggregate function."

i was wondering there was another way to order by favourite count...maybe by using another query in conjuction etc etc

cheers

yes, join doesnt work, but inner join does

ill have a play around with the queries and let you know what happens

cheers

I don't work with Access, so I'm guessing. Perhaps you have to use inner join instead of join. Also, Access might not support subqueries in the from clause.

To trouble shoot, see if this works:
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav

That will test the join vs inner join possibility.

If that works, it's probably the subquery. In that case, you can try joining to artists_fav twice. You give them different alias names. Or, you can move the subquery to the where clause.

have changed query to this, but still same error message
<cfquery name="sim_art_2" datasource="#APPLICATION.Datasource#">
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav
JOIN
(
SELECT id_usr_fav from artists_fav
WHERE id_usr_fav <> #SESSION.auth.idnumber#
AND f1.id_artkey_fav= #url.id_art#
)
f2 on fl.id_usr_fav = f2.id_usr_fav
</cfquery>

cheers

It's the double quotes. If the field is numeric, and id fields should be, you use no quotes. It the field is text, you use single text.

hi, have changed my query to

<cfquery name="sim_art_2" datasource="#APPLICATION.Datasource#">
SELECT bandname_art, count(bandname_art)as favourite_count
FROM artist_art
JOIN artists_fav
ON artist_art.id_art = f1.artists_fav.id_artkey_fav
JOIN
(
SELECT id_usr_fav from artists_fav
WHERE id_usr_fav <>"#SESSION.auth.idnumber#"
AND f1.id_artkey_fav="#url.id_art#"
)
f2 on fl.id_usr_fav = f2.id_usr_fav
</cfquery>

but am unfortunately now getting this error message

http://www.musicexplained.co.uk/index_artist_1.cfm?id_art=27&CFID=83776&CFTOKEN=57625191

cheers for this, just on way out.....will let you know if this works for me when i try tommorrow, just wanted to say thanks as you have been very helpful in the past on this forum

select artist_name, count(artist_name) favourite_count

from artist a join fav on a.artist_id = f1.id_artist_favtable
join
(select id_usr_favtable
from fav
where id_usr_favtable <> "your session number"
and f1.id_artist_favtable = "the one from the url"
) f2 on f1.id_usr_favtable = f2.id_usr_favtable

group by artist_name
order by favourite_count
having favourite_count > some_number