Using both JOIN and UNION ALL

Databases
1

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;
2

I would do it something like this (put the UNION in a sub-query, then join the Users table):


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;
3

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.

4

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

using [code]…[/code] tags

:slight_smile:

p.s. dave, very nice query !

5

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.

Thanks! :slight_smile:

6

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!


		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;
7

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.