SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not getting second table column back

    Hi I am using the following query:
    (SELECT topic_id,subject,date FROM $topics_table WHERE board_id = '{$row2['board_id']}') UNION ALL (SELECT reply_id,subject,date FROM $replies_table WHERE board_id = '{$row2['board_id']}') ORDER BY `date` DESC LIMIT 1

    and reply_id column is not showing up when I do the query in phpmyadmin, what is the problem?
    Have a good day.

  2. #2
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not too familiar with union syntax, but I thought I would take a stab at this and learn something Try this query and see what happens. I think that by being more explicit in the way you write sql, you can avoid errors and troubleshoot your code more easily.

    Also, I don't know how relevant this is, but regarding your ORDER BY clause, the manual says:
    This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY, or else refer to the column in the ORDER BY using its column position. (An alias is preferable because use of column positions is deprecated.)
    You didn't mention anything about your ORDER BY, but I noticed this and thought maybe it would help you write sql with improved syntax.

    Let us know what happens. I hope I'll learn something new!

    Code:
    (SELECT tt.topic_id, tt.subject, tt.date
        FROM $topics_table tt
        WHERE tt.board_id = '{$row2['board_id']}')
      
      UNION ALL
      
      (SELECT tt.reply_id, tt.subject, tt.date
        FROM $replies_table tt
        WHERE tt.board_id = '{$row2['board_id']}')
             
             ORDER BY tt.date DESC LIMIT 1
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  3. #3
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I changed it to this:
    Code:
    SELECT topics_support.topic_id, topics_support.subject, `date` FROM topics_support WHERE topics_support.board_id = '2') UNION ALL (SELECT replies_support.reply_id, replies_support.subject, `date` FROM replies_support WHERE replies_support.board_id = '2') ORDER BY `date` DESC
    And I get an error:
    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') UNION ALL (SELECT replies_support.reply_id, replies_support.s
    Have a good day.

  4. #4
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of MySQL are you running?
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  5. #5
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, I did some testing, and I learned something very interesting.

    I performed a similar query using my database server (MySQL 4.0.20) and received this result:

    Query:
    Code:
    ( SELECT cu.cust_id, cu.firstname, cu.lastname
      FROM customers cu
      WHERE cu.cust_id =  '1007'
      )
      UNION  ALL (
      SELECT ci.client_id, ci.zipcode, ci.city
      FROM contact_info ci
      WHERE ci.client_id =  '6'
      )
    and received this result:
    Code:
    | cust_id | fistname | lastname |
       |  6   |   81050  | Rocky Ford |
    cust_id is actually a 4 digit number, a firstname is not supposed to be a zipcode, and lastname is not the name of a city. The query joined the columns of the first query to the results of the second query.

    So, you probably ARE getting the result you need, you just don't realize it.

    Does this make sense? Let us know what you find out.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  6. #6
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Um if it doesnt work in PhpMyAdmin it aint gonna work in my code.
    Have a good day.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by garlinto
    So, you probably ARE getting the result you need, you just don't realize it.
    exactly!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I need the correct query please .
    Have a good day.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have the correct query

    see post #1

    do you understand how UNION works?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It cannot be correct, as it is not outputting any data on my page.
    Have a good day.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, let's take a deep breath and start over

    do you know how a UNION query works?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ISNT THERE ANY WAY IN MYSQL WHERE I CAN SUCCESSFULLY ADD ANOTHER TABLES ROWS TO CURRENT TABLES ROWS? I mean why would it not work ??
    Have a good day.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    darren, i'm sorry, someone else will have to help you

    i know you are frustrated, but i have asked you the same question twice now and you are ignoring me

    perhaps you might benefit from a simple database or mysql tutorial

    until then, good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Darren, we are only trying to help.

    Maybe it would help you and us if you explain what exactly you are trying to do. It appears that the query you have, while executing correctly, is not the solution to your problem.

    It sounds like you do not understand what a UNION does, and it may be that a UNION is not going to return the type of record set you need.

    Explain what you are trying to do, and maybe we can still help. I feel that it would be such a waste not to utilize r937's help, since he has proven to be quite an asset to us here and is an obvious SQL expert.

    You'll go much further if you start out with the assumtion that you may not know what you are doing in this case, as opposed to the stance that we are wrong since the answer provided here was not what you expected.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  15. #15
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright I am trying to join two tables together, so it combines both sets of rows into 1 table, like thistable +thattable = newtable, however it is not working.
    Have a good day.

  16. #16
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will get much further if you actually supplied data. I mentioned this to you in another thread. I'm not sure why you are reluctant to show data.

    Show a few rows in each table, then show the output you expect. Then in pseudocode perhaps explain how you think you should get there.

    I frequent three boards on a regular basis that r937 posts on. He's closing in on 20,000 posts total. Just there! He does not mind helping you get to a result, but isn't likely to do it for you.

    Help is available, but knowing how to ask for it, and helping out with sample data will ALWAYS get you to a solution quicker than not.

  17. #17
    I want my 4th arrow! garlinto's Avatar
    Join Date
    Jun 2002
    Location
    Riding the electron wave
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, glad to help

    Throw this query into phpMyAdmin and see what you get:

    Code:
    SELECT ts.topic_id, ts.subject, ts.date, rs.reply_id, rs.subject, rs.date
         FROM topics_support ts
         LEFT JOIN replies_support rs
       	ON ts.board_id = rs.board_id
         WHERE ts.board_id = '2'
       ORDER BY rs.date DESC
    I find that it is much easier to use column aliases, since you don't have to type near as much.
    Ducharme's Axiom: "If you view your problem closely
    enough, you will recognize yourself as part of the problem."


  18. #18
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SECRET
    Last edited by Darren884; Feb 2, 2005 at 19:31.
    Have a good day.

  19. #19
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I give up, this should be a simple task with a database.
    Have a good day.

  20. #20
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    YOu are the one making things hard. we've asked you to provide data, you have yet to do it. You have provided your table layout not actual rows of data.

    You also haven't answered Rudy's question on whether or not you understand how a UNION works.

    Had you done those things you would have likely had an answer some time ago.

  21. #21
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats alright I did it a diff way.
    Have a good day.

  22. #22
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good luck getting help with future questions.


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
  •