Using UNION

[lurtnowski]

I have

SELECT manufacturer AS blade_server_manufacturer,model AS blade_server_model
		  FROM blade_servers 
		  INNER JOIN blade_server_types ON blade_servers.blade_server_type_id = blade_server_types.blade_server_type_id WHERE new = 1

which works and returns 1 result

and

		  SELECT manufacturer AS kvm_manufacturer,model AS kvm_model 
		  FROM kvms 
		  INNER JOIN kvm_types ON kvms.kvm_type_id = kvm_types.kvm_type_id 
		  WHERE new = 1

which also works and returns 1 result

but when I try to combine them,

SELECT manufacturer AS blade_server_manufacturer,model AS blade_server_model
		  FROM blade_servers 
		  INNER JOIN blade_server_types ON blade_servers.blade_server_type_id = blade_server_types.blade_server_type_id WHERE new = 1
		  UNION
		  SELECT manufacturer AS kvm_manufacturer,model AS kvm_model 
		  FROM kvms 
		  INNER JOIN kvm_types ON kvms.kvm_type_id = kvm_types.kvm_type_id 
		  WHERE new = 1

the result is,

shouldnt it be the 2 queries combined…so 2?

[Martyr2]

Take a quick gander at https://dev.mysql.com/doc/refman/8.0/en/union.html for a minute. There are two things to look at… the first is the section titled " Result Set Column Names and Data Types" which tells you that MySQL is going to take the columns of the first select. Which is why you see your blade titles. The second section of note is “UNION DISTINCT and UNION ALL” where in the first sentence is mentions that by default rows with matching values are removed. Since the values of both tables have the values “Unknown” the second row is dropped.

By default, duplicate rows are removed from UNION results. The optional DISTINCT keyword has the same effect but makes it explicit. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

Try UNION ALL and see if that works for you.