CODEWITHSUNDEEP
mysqlsqlselectsum
Jojo George
Jojo George

Reputation: 159

union of two select queries with different fields

I have two tables empmaster and allocation. I used union to do sql operation in order to get results from two tables. empmaster has empid and other empdetails. Table allocation contains empid from empmaster as foriegn key another field called per_alloc. I need to retrieve empdetails which satisfies:

  1. empmaster.empid not in allocation.empid.

  2. empmaster.empid in allocation.empid and allocation.per_alloc < 100.

MySQL query I used is:

  select distinct(tbl_empmaster.emp_fname) 
  from tbl_empmaster 
  where tbl_empmaster.emp_id not in(select tbl_allocation.emp_id 
                                    from tbl_allocation)
  union
  select distinct(tbl_empmaster.emp_fname) 
  from tbl_empmaster 
   where tbl_empmaster.emp_id in(select tbl_allocation.emp_id 
                                 from tbl_allocation  
                                 group by emp_id  
                                 having sum(per_alloc) < 100)

This only retrieves empdetails, say tbl_empmaster.emp_fname, I need to retrieve sum(per_alloc) from select tbl_allocation!!! When I tried it gives lot of errors, Can any one show me the correct way, please?

Upvotes: 2

Views: 1691

Answers (2)

digvijay91
digvijay91

Reputation: 697

Ok, from what I have understood of your problem, I see two problems.

  1. There is the unecessary grouping in the subquery of the second select statement. It should be ok to just write
    select tbl_allocation.emp_id from tbl_allocation where tbl_allocation.per_alloc<100)*
  2. And the answer to your question.change the second select statement to the following, and it should work:
    select A.emp_fname, B.per_alloc from tbl_empmaster A join tbl_allocation B using(emp_id) where A.emp_id in(select C.emp_id from tbl_allocation C where C.per_alloc<100))

**Assuming that emp_id is the primary key*

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT DISTINCT em.emp_fname, 0 alloc 
FROM tbl_empmaster em
WHERE em.emp_id NOT IN(SELECT emp_id FROM tbl_allocation)
UNION
SELECT DISTINCT em.emp_fname, SUM(a.per_alloc) alloc
FROM tbl_empmaster em 
INNER JOIN tbl_allocation a ON em.emp_id = a.emp_id 
GROUP BY a.emp_id 
HAVING SUM(a.per_alloc)<100

Upvotes: 1

Related Questions