SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join two tables and create a new sortable column

    Hi

    I'm just getting my head around joins and have come up against the limits of my knowledge (which didn't take long !)

    I have two tables in one DB. I would like to take an entry date column(datetime format) from both tables and merge them into one so I can sort them by date to echo out the contents of a blog.

    I've expereimented with joins but can't get it to work. I've read about unions and can see the benefits of a temporary table possibly.

    Any advice to point me in the right direction would be much appreciated.

    Thanks in anticipation

  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)
    the right direction? pretty hard based on the (lack of) information you've posted

    Code:
    SELECT entrydate FROM table1
    UNION ALL
    SELECT entrydate FROM table2
    ORDER
        BY entrydate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Posting the structures of the tables your dealing with and their relationships between columns if not obviously apparent may help. Without any of this type of information your question is to generic and abstract to offer any concrete/useful advice or implementation

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well smack me down. I don't expect full coded solution I merely wanted pointing down the right track but seemingly I've been too vague.

    I have two tables as below (they are a work in progress so although I appreciate comments and hints on how to improve I don't want berating too much)

    Table 1 (it's not actually called table one in case you were wondering)

    Code MySQL:
    Field  	  Type   	     
     
    id 	      int(11) 	          	 
    postedby  int(11) 	       	  	 
    postid 	  int(11) 	         	 
    entrydate datetime 	          	 
    comment   varchar(2000) 	  	 
    viewed 	  int(11) 	         	 
    ip 	      varchar(25)


    Table 2

    Code MySQL:
    Field  	    Type   	       
     
    id 	        int(11) 	          	  	 
    postedby 	int(11) 	         	  	 
    entrydate   datetime 	          	  	 
    heading 	varchar(20) 	  	  	 
    tip 	    varchar(2000) 	  	  	 
    ip 	        varchar(25)

    What I wanted to do was somehow query the DB and iterate the result to echo out via a PHP script a list of tips and comments by a given user (identified by postedby in table 1 & table 2) and use the entrydate (from both tables) to order the list to display them chronologically.

    Following r937's advice I tried the following:

    Code MySQL:
    'SELECT id, heading, tip, entrydate FROM energypost'
            . ' UNION '
            . ' SELECT postid, ' ', comment, entrydate FROM energycomment'
            . ' ORDER'
            . ' BY entrydate asc'
            . ' '

    Note, postid in table 2 matches the id of table one to make an association when adding comments. The ' ' seems to allow null values and matches column numbers when the quey runs.

    which seems, at this stage, to provide the solution I was looking for.

    Thanks for your assistance so far, much appreciated. Any improvements, help advice or comments are always most welcome..........

  5. #5
    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)
    use UNION ALL instead of UNION, to avoid an unnecessary sort

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi and once again thanks.

    Just one more question if anyone has the time. In the resulting table that is produced by the "union" the column names seem to be adopted from table 1 and the data from the columns in table 2 is merged into the same column, for example the following code:

    Code MySQL:
    SELECT 
    tip, entrydate FROM energypost
    UNION ALL
    SELECT 
    comment, entrydate   FROM energycomment
    ORDER
    BY entrydate asc

    produces the following result:

    tip

    Shut Curtains
    Thats rubbish !!!
    Keep Windows closed
    Mine don't open !!!
    Mine don't open....Have I already said this ??
    I can't afford curtains...
    ZZzzzzz

    (I've omitted the entrydate result as that works as required) Is there anyway to produce two columns x1 tip column and x1 comments column sorted by entrydate ?

  7. #7
    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 tip, NULL as comment, entrydate FROM energypost
    UNION ALL
    SELECT NULL AS tip, comment, entrydate FROM energycomment
    ORDER BY entrydate ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, that's spot on. Thank you very much.

    Did you learn all this by yourself or have you had some formal education/training ? (If you don't mind me asking)

  9. #9
    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)
    no, i don't mind you asking

    all i did was read da manual and practice, practice, practice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    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)
    Now he walks on water! Well in winter at least.

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well you have my greatest respect !

    Either you're highly motivated and dedicated or somewhat obsessional lol.

    Many thanks for taking the time and helping me out, much appreciated.

    Regards

    Mark H


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
  •