SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Using both JOIN and UNION ALL

    The following UNION is working perfectly. But it sure would be nice if I could also use a JOIN to obtain the firstName and lastName from the users table. Is this possible?

    Thanks!!


    (SELECT date_format(answerDate, '%m-%d-%Y') as dateAdded
    , answerDate AS sortDate
    , uID
    , cID AS entryID
    , 'campaigns' AS source
    , concat(type, ' Campaign') as type
    , answer AS entry
    , status
    , dateDue
    FROM campaigns
    WHERE status = 'Open')

    UNION ALL

    (SELECT date_format(dateAdded, '%m-%d-%Y') as dateAdded
    , dateAdded AS sortDate
    , uID
    , cID as entryID
    , 'communications' AS source
    , type
    , entry
    , status
    , dateDue
    FROM communications
    WHERE status = 'Open')
    ORDER by sortDate;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I would do it something like this (put the UNION in a sub-query, then join the Users table):

    Code:
    SELECT dateAdded
    	 , sortDate
    	 , uID
    	 , entryID
    	 , source
    	 , type
    	 , entry
    	 , status
    	 , dateDue
    	 , firstName
    	 , lastName
      FROM (SELECT date_format(answerDate, '%m-%d-%Y') as dateAdded
    			 , answerDate AS sortDate
    			 , uID
    			 , cID AS entryID
    			 , 'campaigns' AS source
    			 , concat(type, ' Campaign') as type
    			 , answer AS entry
    			 , status
    			 , dateDue
    		  FROM campaigns
    		 WHERE status = 'Open'
    	 	 UNION ALL
    		SELECT date_format(dateAdded, '%m-%d-%Y') as dateAdded
    			 , dateAdded AS sortDate 
    			 , uID
    			 , cID as entryID
    			 , 'communications' AS source
    			 , type
    			 , entry
    			 , status
    			 , dateDue
    		  FROM communications
    		 WHERE status = 'Open') SQ
     JOIN Users U ON U.UID = SQ.UID
    ORDER by sortDate;
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for helping me here, however, I get the following error, "Column 'uID' in field list is ambiguous". By the way, how do you format text in these posts so that it maintains tabs and formatting. Whenever I post something in here it pushes all the text to the left and eliminates the tabs that I had in place.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    "Column 'uID' in field list is ambiguous".
    the field list is the list of columns in the SELECT clause of your outer query

    as you can see in the ON clause of the outer query's JOIN, there are two different columns with that same name... it actually shouldn't matter which one it is (since they're going to be equal) but you have to pick one of them anyway

    Quote Originally Posted by busboy View Post
    By the way, how do you format text in these posts so that it maintains tabs and formatting.
    using [code]...[/code] tags



    p.s. dave, very nice query !
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    the field list is the list of columns in the SELECT clause of your outer query

    as you can see in the ON clause of the outer query's JOIN, there are two different columns with that same name... it actually shouldn't matter which one it is (since they're going to be equal) but you have to pick one of them anyway
    Crap - shoulda caught that - I would use sq.uID, but that's just me - doesn't matter which you pick (sq.uID or u.uID) as they're the same, but you need to pick one.

    Quote Originally Posted by r937 View Post
    p.s. dave, very nice query !
    Thanks!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I got things working and then decided to UNION ALL one additional table, called notesOffline. I get the query to actually run when I use the 'blank' as uID, however, no results are actually included from this table because notesOffline doesn't have a uID column. Is there any other way I can UNION ALL these tables together? Below is the query how I currently have it.

    Thanks!

    Code:
    		SELECT dateAdded
    			 , dateAddedFormatted
    			 , u.uID
    			 , concat (firstName, ' ', lastName) as fullName
    			 , memberType
    			 , subscription
    			 , sponsorID
    			 , dateDue
    			 , dateDueFormatted
    			 , entryID
    			 , source
    			 , type
    			 , topic
    			 , entry
    			 , status
    			 , dateDue
    		 FROM (SELECT answerDate as dateAdded
    		 			 , date_format(answerDate, '%m-%d-%Y') as dateAddedFormatted
    					 , uID
    					 , cID AS entryID
    					 , 'campaigns' AS source
    					 , 'Campaign' as type
    					 , type as topic 
    					 , answer AS entry
    					 , status
    					 , answerDate as dateDue
    					 , date_format(answerDate, '%m-%d-%Y') as dateDueFormatted
    				  FROM campaigns
    				 WHERE status = 'Open' and (type = 'Enrollment' and uID in (select uID from users where sponsorID = 333 or sponsorID = 0) or type = 'Gold')
    				UNION ALL	
    
    
    				SELECT dateAdded as dateAdded
    					 , date_format(dateAdded, '%m-%d-%Y') as dateAddedFormatted
    					 , 'blank' as uID
    					 , nID as entryID
    					 , 'notesOffline' AS source
    					 , type
    					 , topic as topic
    					 , entry
    					 , status
    					 , dateDue
    					 , date_format(dateDue, '%m-%d-%Y') as dateDueFormatted
    				  FROM notesOffline
    				 WHERE status = 'Open'
    				
    				UNION ALL								
    				SELECT dateAdded as dateAdded
    					 , date_format(dateAdded, '%m-%d-%Y') as dateAddedFormatted
    					 , uID
    					 , cID as entryID
    					 , 'communications' AS source
    					 , type
    					 , topic as topic
    					 , entry
    					 , status
    					 , dateDue
    					 , date_format(dateDue, '%m-%d-%Y') as dateDueFormatted
    				  FROM communications
    				 WHERE status = 'Open') SQ
    		JOIN users u 
    		ON u.uID = SQ.uID 
    		ORDER by dateDue;
    Convert your dollars into silver coins. www.convert2silver.com

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Nevermind guys, I replaced 'blank' with an actual uID from the users table. Now the rows from the notesOffline table are showing. I just need to accommodate for this in my PHP code by replacing that member name with "Name Unknown" and all will be well. Thanks again.
    Convert your dollars into silver coins. www.convert2silver.com


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
  •