#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    11
    Rep Power
    0

    Question about getting data from 2 tables..


    I'm trying to query 2 tables. I have a questions table and i have a table with prizes. In my questions table i have a field where i store the prize ID. With that ID i want to select the prize name out of the prizes table. Problem i now have is that in the questions table I have multiple questions and multiple prize ID's (every question can contain a prize). What happens now is that i just get 1 prize name output, where there should be at least 2 (i have 3 questions, 2 contain a prize. The number of questions can get up to 100 and to every question a prize can be added, but doesn't have to.


    This is the code i'm using.

    Code:
    <cfquery name="GetQuestionPrizes" datasource="#DS#">
    SELECT prize
    FROM questions
    WHERE prize <> ''
    </cfquery>
    If i output the above code, it gives me 2 results, as it should.

    I then do another query:

    Code:
    <cfquery name="GetPrizes" datasource="#DS#">
    SELECT *
    FROM prizes
    WHERE id = #GetQuestionPrizes.prize#
    </cfquery>
    This just outputs 1 result while it should be 2.

    i tried to do 1 query with both tables in it like:

    Code:
    <cfquery name="GetQuestionPrizes" datasource="#DS#">
    SELECT *
    FROM questions, prizes
    WHERE questions.prize <> ''
    AND questions.prize = prizes.id
    </cfquery>
    this outputs 0 result.


    Any idea on how to solve this problem ?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    11
    Rep Power
    0
    I also tried a subquery

    Code:
    <cfquery name="GetPrizeName" datasource="#DS#">
    SELECT prize
    FROM questions
    WHERE exists (select * from cprizes where questions.prize = 'cprizes.id')
    </cfquery>
    but this also gives no result
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    11
    Rep Power
    0
    topic can be closed, got the answer..

IMN logo majestic logo threadwatch logo seochat tools logo