SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    making a mess of my sql union statement

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

    Code:
    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

    Code:
     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
    Live Long Laugh Often

  2. #2
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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
    Live Long Laugh Often


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •