Har denna

SELECT smyg_userInfo.*, smyg_where.*, smyg_users.*, smyg_relations.*, smyg_relationTypes.* FROM smyg_relations JOIN smyg_users ON smyg_users.id = smyg_relations.userID JOIN smyg_relationTypes ON smyg_relationTypes.id = smyg_relations.relationType JOIN smyg_where ON smyg_where.userID = smyg_relations.personID JOIN smyg_userInfo ON smyg_userInfo.userID = smyg_relations.personID WHERE smyg_relations.userID = " & session("user")

vill ha in en count(smyg_photos.*) as photocount where smyg_photos.userID = smyg_relations.personID i den

<code>
SELECT smyg_userInfo.*,
smyg_where.*,
smyg_users.*,
smyg_relations.*,
smyg_relationTypes.* ,
(select count(*)
from smyg_photos
where smyg_photos.userID = smyg_relations.personID ) as photoCount
FROM smyg_relations JOIN smyg_users ON smyg_users.id = smyg_relations.userID
JOIN smyg_relationTypes ON smyg_relationTypes.id = smyg_relations.relationType
JOIN smyg_where ON smyg_where.userID = smyg_relations.personID
JOIN smyg_userInfo ON smyg_userInfo.userID = smyg_relations.personID
WHERE smyg_relations.userID = " & session("user")
</code>

Funkar inte.. har nu den här koden

[code]
sql = "SELECT " _
& "smyg_userInfo.socialID, smyg_userInfo.gender, smyg_where.userID, smyg_where.eventTime, smyg_users.username, smyg_users.id, smyg_relations.userID, smyg_relations.personID, smyg_relationTypes.id, smyg_relationTypes.title " _
& "(SELECT " _
& "count(*) " _
& "FROM " _
& "smyg_photos " _
& "WHERE " _
& "smyg_photos.userID = smyg_relations.personID) as photocount " _
& "FROM " _
& "smyg_relations " _
& "INNER JOIN " _
& "smyg_users ON smyg_users.id = smyg_relations.userID " _
& "INNER JOIN " _
& "smyg_relationTypes ON smyg_relationTypes.id = smyg_relations.relationType " _
& "INNER JOIN " _
& "smyg_where ON smyg_where.userID = smyg_relations.personID " _
& "INNER JOIN " _
& "smyg_userInfo ON smyg_userInfo.userID = smyg_relations.personID " _
& "INNER JOIN " _
& "smyg_photos ON smyg_photos.userID = smyg_relations.personID " _
& "GROUP BY " _
& "smyg_userInfo.socialID, smyg_userInfo.gender, smyg_where.userID, smyg_where.eventTime, smyg_users.username, smyg_users.id, smyg_relations.userID, smyg_relations.personID, smyg_relationTypes.id, smyg_relationTypes.title " _
& "HAVING " _
& "smyg_relations.userID = " & session("user")
[/code]

Funkar inte är en usel problembeskrivning.

Du har glömt ett komma

<code>
smyg_relationTypes.title
</code>

Funkar inte = 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 count(*) FROM smyg_photos WHERE smyg_photos.userID = smy

komma inlagt för andra select delen


tillägg: använder mySQL 4.0.nåt

Nästlade frågor stöds inte förrän i 4.1.

(då har jag 4.1.nåt då.. antar jag)

Löste det

Här är SQL strängen

		sql = "SELECT " _
			& "smyg_userInfo.socialID, " _
			& "smyg_userInfo.gender, " _
			& "smyg_where.userID, " _
			& "smyg_where.eventTime, " _
			& "smyg_users.username, " _
			& "smyg_users.id, " _
			& "smyg_relations.userID, " _
			& "smyg_relations.personID, " _
			& "smyg_relationTypes.id, " _
			& "smyg_relationTypes.title, " _
			& "count(smyg_photos.id) as photocount " _
		& "FROM " _
			& "smyg_relations " _
		& "INNER JOIN " _
			& "smyg_users ON smyg_users.id = smyg_relations.personID " _
		& "INNER JOIN " _
			& "smyg_relationTypes ON smyg_relationTypes.id = smyg_relations.relationType " _
		& "INNER JOIN " _
			& "smyg_where ON smyg_where.userID = smyg_relations.personID " _
		& "INNER JOIN " _
			& "smyg_userInfo ON smyg_userInfo.userID = smyg_relations.personID " _
		& "LEFT OUTER JOIN " _
			& "smyg_photos ON smyg_photos.userID = smyg_relations.personID " _
		& "GROUP BY " _
			& "smyg_userInfo.socialID, " _
			& "smyg_userInfo.gender, " _
			& "smyg_where.userID, " _
			& "smyg_where.eventTime, " _
			& "smyg_users.username, " _
			& "smyg_users.id, " _
			& "smyg_relations.userID, " _
			& "smyg_relations.personID, " _
			& "smyg_relationTypes.id, " _
			& "smyg_relationTypes.title " _
		& "HAVING " _
			& "smyg_relations.userID = " & session("user")

Jag skulle strukturerar det något sånt här:

sql = "SELECT " & _
            "smyg_userInfo.socialID, " & _
            "smyg_userInfo.gender, " & _
            "smyg_where.userID, " & _
            "smyg_where.eventTime, " & _
            "smyg_users.username, " & _
            "smyg_users.id, " & _
            "smyg_relations.userID, " & _
            "smyg_relations.personID, " & _
            "smyg_relationTypes.id, " & _
            "smyg_relationTypes.title, " & _
            "photocount " & _
        "FROM " & _
            "smyg_relations " & _
        "INNER JOIN " & _
            "smyg_users ON smyg_users.id = smyg_relations.personID " & _
        "INNER JOIN " & _
            "smyg_relationTypes ON smyg_relationTypes.id = smyg_relations.relationType " & _
        "INNER JOIN " & _
            "smyg_where ON smyg_where.userID = smyg_relations.personID " & _
        "INNER JOIN " & _
            "smyg_userInfo ON smyg_userInfo.userID = smyg_relations.personID " & _
        "LEFT OUTER JOIN " & _
            "(SELECT " & _
                "smyg_photos.userID, " & _
                "Count(*) as photocount " & _
            "FROM smyg_photos " & _
            "GROUP BY smyg_photos.userID) Sub ON smyg_relations.personID = Sub.userID " & _
        "WHERE " & _
            "smyg_relations.userID = " & session("user")