combine 2 select queries in mysql

Question

I have 2 select statements:

timestamp of emp getting awards for specific emp id

SELECT * FROM user_table,employeetable,awards where user_table.empid=employeetable.empid AND user_table.empid=awards.empid AND user_table.empid=123 ORDER BY timestamp DESC
All employees staying around 25 miles from the current loc:current location: lat =37 lng=-122

SELECT * ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM user_table,employeetable,awards where user_table.empid=employeetable.empid AND user_table.empid=awards.empid HAVING distance < 25 ORDER BY distance;

How do I combine both and ORDER BY timestamp ?btw both have field timestamp.

1.has specific user
2.all users within specific radius

I really appreciate any help.Thanks in Advance.

Gordon Linoff · Accepted Answer · 2014-01-19 20:30:10Z

1

You can combine the two queries into a single query, just using logic in the where clause (which this has turned into a having clause:

select *, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) as distance
from user u join
     employee e
     on u.empid = e.empid join
     awards a
     on u.empid = a.empid
having empid = 123 or distance < 25;

This uses having instead of where so the distance column alias can be used instead of the formula.

answered Jan 19, 2014 at 20:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jason Over a year ago

what if there are more empid. ie: instead of only 123 I have 123,234,567,789,102 .Then will the code above change to IN ?basically when i have group of empids.

Gordon Linoff Over a year ago

Yes, you would have empid in (123, 234, 567, 789, 102) as the first condition.

jason Over a year ago

so I need to replace empid=123 to empid in (123, 234, 567, 789, 102) with HAVING behind empid thats it?

Gordon Linoff Over a year ago

Yes. That is the way.

jason Over a year ago

query:

SELECT * FROM (    SELECT * FROM user u    INNER JOIN employee e ON (u.empid = e.empid)    INNER JOIN awards a ON (u.empid = a.empid)    WHERE u.empid = 123     UNION     SELECT * ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) )+ sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance    FROM user u    INNER JOIN employee e ON (u.empid = e.empid)    INNER JOIN awards a ON (u.empid = a.empid)    HAVING distance < 25    ORDER BY distance ) a ORDER BY timestamp DESC

error "The used SELECT statements have a different number of columns

|

Collectives™ on Stack Overflow

combine 2 select queries in mysql

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related