SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy A MYSQL JOIN and CFOUTPUT Question

    I have 3 tables.

    Table 1 named "T_Questions"

    HTML Code:
    Question_ID(Pk)     Questions             Burger_ID(FK)
    1                   How Much?              1
    2                   Has Seasoning?         1
    3                   Type of Bread?         1
    4                   Where is Spock?        3
    5                   What Bread?            3
    6                   What Sauce?            3
    Table 2 named "T_Comments"

    HTML Code:
    Comments_ID(Pk)     Comments             Burger_ID(FK)
    1                   Too Fatty              1
    2                   Too Small              2
    3                   Perfect Taste!         1
    4                   Too Big                1
    5                   Too Slim!              2
    6                   Too Spacey!            3
    Table 3 named "T_BurgerOrder"

    HTML Code:
    Burger_ID(Pk)     Burger_Name             OrderName
    1                 Mighty Joe Burger        Jerry
    2                 Slim Jim Burger          Florence
    3                 Startreck Burger         Jerry
    This is how I joined the tables:

    <cfquery datasource="#DS#" name="MyBurgers">

    SELECT *
    FROM t_BurgerOrder
    LEFT JOIN t_Questions
    ON T_BurgerOrder.Burger_ID = t_Questions.Burger_ID
    LEFT JOIN T_Comments
    ON T_BurgerOrder.Burger_ID = T_Comments.Burger_ID
    Where OrderName = "Jerry"

    </CFQuery>

    This is the simple code on the page to test my output format.

    <cfoutput query="MyBurgers" Group="Burger_ID">

    #Burger_ID#, <cfoutput>#Comments_ID#</cfoutput> <br/>

    </cfoutput>


    This is what is displayed after the cf code above is run.

    1, (1)(3)(4)(1)(3)(4)(1)(3)(4)
    3, (6)(6)(6)

    As you can see, The output above shows that the Burger with Burger_ID '1' has 3 comments, but the answer repeats 3 times as there are also 3 records

    with that same foreign key 'Burger_ID' in the T_Questions table.

    Similarly, even though Burger with Burger_ID 3 only has 1 comment in the comment table, it spits out the answer 3 times as there are 3 Questions with

    the same Burger_ID in the T_Question table.

    How would i solve this issue so that it only shows each answer once like below?

    1, (1)(3)(4)
    3, (6)

    Thanks so much again for your kind help. I have really been feeling down about this issue and as i am new, I really do not know how to go about

    solving this. Thank you again.

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2007
    Posts
    584
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am new, I really do not know how to go about solving this.
    Welcome! (Fast food! Good for homework assignments. Not so good for cholesterol levels ;-).

    When you get strange or wrong output from a query, a good place to start is by using cfdump. It will show all the data in your query. From that you can at least tell where the problem is: with the query or your cfoutput statement.

    Code:
    <cfquery name="MyBurgers" ...>
    .... the sql for your query ...
    </cfquery>
    
    <cfdump var="#MyBurgers#" label="The contents of your query">
    I suspect you will find the problem is the query. It is probably returning too much data. Since you are joining with the T_Questions table, the results will include one record for _each_ related question_ID. So for Burger_ID #1, it will contain three records:

    Code:
    Question_ID(Pk)     Questions             Burger_ID(FK)
    1                   How Much?              1
    2                   Has Seasoning?         1
    3                   Type of Bread?         1
    That is why your results are repeated three times.

    If you do not need the question data in your output, simply remove the T_Questions JOIN from your query. That should produce the results you need.

    HTH

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for very fast reply cfStarlight, but I do need to have the T_Question table also joined as I need the output to show both the comments and questiosn for each burger.

    For example:

    The Might Joe burger has 3 questions and 3 comments.

    Question 1 = ....
    Question 2 = ...
    Question 3 = ...

    Comment 1 = ...
    Comment 2 = ...
    Comment 3 = ...

    Is this possible?

    Thanks you again.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Burntfromfusion View Post
    Is this possible?
    certainly -- just use two queries

    when you combine two unrelated one-to-many relationships (unrelated to each other, that is), you get these cross-join effects that multiply the results

    since you want the results shown separately, in separate parts of the output page, so you can safely retrieve them separately

    there is no good reason to try to do it in one query, but if you really must, you should use a UNION query -- then you could use two CFOUTPUT loops, with a CFIF in each one to select only the rows for that part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    I have tried using a UNION join as recommended by you, but so far have failed trying to come up with one that works. Would you be able to show me how you would write such a join?

    I tried:

    SELECT t_BurgerOrder.Burger_ID, t_Questions.Questions_ID
    FROM t_BugerOrder
    LEFT JOIN t_Questions
    ON t_BurgerOrder.Burger_ID = t_Questions.Burger_ID
    UNION
    SELECT t_BurgerOrder.Burger_ID, t_Comments.Comments_ID
    FROM t_BurgerOrder
    LEFT JOIN t_Comments
    ON t_BurgerOrder.Burger_ID = t_Comments.Burger_ID

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here's the query:
    Code:
    (
    SELECT t_BurgerOrder.Burger_ID
         , 'Question'                 AS rowtype
         , t_Questions.Questions_ID   AS ID
         , t_Questions.Questions      AS content
      FROM t_BurgerOrder
    INNER
      JOIN t_Questions
        ON t_Questions.Burger_ID = t_BurgerOrder.Burger_ID
     WHERE t_BurgerOrder.OrderName = 'Jerry'
    )
    UNION ALL
    (
    SELECT t_BurgerOrder.Burger_ID
         , 'Comment' AS rowtype
         , t_Comments.Comments_ID
         , t_Comments.Comments
      FROM t_BurgerOrder
    INNER
      JOIN t_Comments
        ON t_Comments.Burger_ID = t_BurgerOrder.Burger_ID 
     WHERE t_BurgerOrder.OrderName = 'Jerry'
    )
    ORDER
        BY Burger_ID
         , rowtype
    and here's the CFOUTPUT:
    Code:
    <CFOUTPUT QUERY="myunion" GROUP="Burger_ID">
    Burger: #Burger_ID#
    <CFOUTPUT>
    #rowtype#: #ID# #content#
    </CFOUTPUT>
    </CFOUTPUT>
    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Dear r937,

    I cannot believe it! You got my question to work! You are the greatest thing to me since the discovery of milk & Honey!

    What I asked was a simplified version of my database structure. Let me use your method to see if I can make it work on my more complex version that has 7 tables linked together.

    But, I am very very very grateful for your kind help!


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
  •