SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    United Kingdom
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Bad join resulting in Wrong Results

    I'm trying to find the best structure for my tables and as a consequence normalise them. With this specific web page I am trying to display the results of a monthly prize draw. (Each draw has 2 cash prizes). I am having problems in displaying the results properly though.

    The draw table is as follows:
    draw_id (eg. 1)
    draw_date (eg. 2004-12-12)
    draw_winner1 (eg. 4)
    draw_winner2 (eg. 5)
    draw_first_prize (eg. 25)
    draw_second_prize (eg. 5)

    The draw winners are always from the members table

    member_id (eg. 4)
    member_firstname (eg. Mark)
    member_surname (eg. Smith)
    member_email (eg. mark@smooh.com)
    member_primary_telephone (eg. 01156665555)
    member_description (eg. Mark is so funny it's unbelievable!)

    My first problem is how to relate the draw table to a member from the members table. And then based on this what will the SQL statement look like.

    My ugly attempt looks like this:
    SELECT member_firstname, member_surname, DATE_FORMAT(draw_date, '%M %Y') AS draw_date, draw_first_prize, draw_second_prize FROM members, club_draw WHERE draw.draw_winner1 = members.member_id ORDER BY draw_date DESC

    If I do it this way all I get when I echo it is:
    Code:
    February 2005
    25 - Bruce Dacker
    5 - Bruce Dacker
    
    December 2004
    25 - Mark Smith
    5 - Mark Smith
    The problem is the WHERE clause which just results in the prize winner being repeated even though they haven't won both prizes. Does anyone know how I should solve this?

    BTW I'm aiming to have another table for the prizes, but that's irrelevant at the moment.
    prize_id (eg. 1)
    prize_amount (eg 25)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select date_format(d.draw_date, '%m %y') as draw_date
         , d.draw_first_prize
         , w1.member_firstname as winner1_firstname
         , w1.member_surname   as winner1_surname
         , d.draw_second_prize 
         , w2.member_firstname as winner2_firstname
         , w2.member_surname   as winner2_surname
      from club_draw as d
    inner
      join members as w1
        on d.draw_winner1 = w1.member_id 
    inner
      join members as w2
        on d.draw_winner2 = w2.member_id 
    order 
        by draw_date desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    United Kingdom
    Posts
    346
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Magic r937

    I don't think I realised just how much should go into an SQL query in order to do what at first seems quite an easy job.

    Although I understand everything that you've done (i think) it does seem quite complex really to a JOIN beginner. But I'm glad you've given me an example of proper joining which I can now relate to, so thanks a lot!


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
  •