Getting values from 2 tables with different conditions for both tables

Table 1

ID	FirstName	LastNmae	city	Group	code
11	john	smith	abc	E	P
21	don	davis	def	E	P
3	vee	miller	ghi	Q	P
6	vee	miller	ghi	Q	P

Table 2

ID	FirstName	LastNmae	city	Status	EmpName	Phone
11	john	smith	abc	U	Company 1	123
21	don	davis	def	P	Company 2	456
3	vee	miller	ghi	C	Company 3	789
4	jim	jones	xyz	P	comapany4	001

I have 2 tables mentioned above. I need an output from both table under these conditions

For table 1 condition is: Group='E' AND code='P'

For table 2 condition is : Status = 'U' OR Status = 'P'

For output required columns are: ID, FirstName, LastName, City, EmpName, Phone

I cannot use UNION because number of columns mismatch.

Desired Output:

ID	FirstName	LastNmae	city	EmpName	Phone
11	john	smith	abc	Company 1	123
21	don	davis	def	Company 2	456
4	jim	jones	xyz	comapany4	001

How can i get desired output. With UNION i can’t get “EmpName” and “Phone” column. Is there anyway to use JOIN to get desired output.

Answer

I think you still need UNION. Try this query:

SELECT ID, FirstName, LastName, city, EmpName, Phone
FROM table2
WHERE Status IN ('U', 'P')

UNION

SELECT t1.ID, t1.FirstName, t1.LastName, t2.city, t2.EmpName, t2.Phone 
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.ID = t2.ID
WHERE t1.Group = 'E' AND t1.code = 'P'
;