making a mess of my sql union statement

[chrissie76]

Hi,
I am trying to compose a sql statement, a the moment i have this:

SELECT user.username, user.outcode, item.ts_added, item.image, item.details, item.title, user.showlocation, postcodes.Latitude, postcodes.Longitude, item.thumb, 
FROM item
LEFT JOIN user ON item.user_id = user.user_id
LEFT JOIN postcodes ON postcodes.outcode = user.outcode

WHERE item.item_id =57

and it works a treat
what i also need to do is run a select count on another table that is related.
This table is called `given` and there are three fields
the item number in question (given.item_id) which is largely irrelevant to this particular query
and the user id of the receiver and the giver (given.user_idrec) and (given.user_idgiv)
and what i want to do is from the first query, find out how many times that user appears against a giver and a receiver,
I've tried all sorts of things and the most sucess i have had is with a subquery but this only works on one or the other (due to the ON clause joining the table)

I do hope this makes sense and that someone out there knows how i should go about this, i have a book on sql on order from amazon but its taking forever and i need this for my dissy project.

Many Thanks in advance, chrissie

oh and this is my code that doesnt quite do what i want... so you can see what i've been trying, this only gives me the count on whatever is named in the ON clause

 SELECT user.username, user.outcode, item.ts_added, item.image, item.details, item.title, user.showlocation, postcodes.Latitude, postcodes.Longitude, item.thumb, COUNT( given.user_idgiv ) AS giventimes, COUNT( given.user_idrec ) AS receivedtimes
FROM item
LEFT JOIN user ON item.user_id = user.user_id
LEFT JOIN postcodes ON postcodes.outcode = user.outcode
LEFT JOIN given ON given.user_idrec = user.user_id
WHERE item.item_id =57
LIMIT 0 , 30

[chrissie76]

ok, just to let you know i have managed to fix this myself 8-)
and if anyone else has a similiar issue this is how i did it

SELECT user.username, user.outcode, item.ts_added, item.image, item.details, item.title, user.showlocation, postcodes.Latitude, postcodes.Longitude, item.thumb, user.user_id, (

SELECT COUNT( given.user_idgiv ) AS giventimes
FROM `given` 
WHERE given.user_idgiv = user.user_id
) AS giventimes, (

SELECT COUNT( given.user_idrec ) AS rectimes
FROM `given` 
WHERE given.user_idrec = user.user_id
) AS rectimes
FROM item
LEFT JOIN user ON item.user_id = user.user_id
LEFT JOIN postcodes ON postcodes.outcode = user.outcode
WHERE item.item_id =57
LIMIT 0 , 30