1

I have multiple queries like this:

select id,name from phonebook WHERE (name='') AND (Tag ='') AND Country= ''; 
select id,name from phonebook WHERE (name='') AND Country= '';
select id,name from phonebook WHERE (tag='') AND Country= '';

I want to join them in one query (in this order).

EIDT

I want put order by in each query (not for all):

select id,name from phonebook WHERE (name='') AND (Tag ='') AND Country= '' order by points;

so who get higher points in each query will be first

Rustam
  • 17
  • 5

2 Answers2

1
select id, name, case when name='' and Tag ='' then 'all_empty'
                      when name='' then 'name_empty'
                      when Tag ='' then 'tag_empty'
                 end as result
from phonebook 
WHERE Country= '' and (name='' or Tag ='')
order by result
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

You are probably looking for unions if you want to join result of multiple queries or if you want to achieve just your solution the go ahead with above answer(order needs to be taken care of though):

select id,name from phonebook WHERE (name='') AND (Tag ='') AND Country= '' UNION
select id,name from phonebook WHERE (name='') AND Country= '' UNION
select id,name from phonebook WHERE (tag='') AND Country= '';

As suggested by MatBailie in comment if you want duplication resulted in each query to remain than use UNION ALL instead of UNION.

MatBailie : UNION will search through the results and remove duplicates. UNION ALL won't even make that attempt. In this case we know in advance the three queries don't have the same results, so UNION ALL will remove some redundant CPU effort. Also, UNION can cause the ordering to change (do to the de-duplication), where as UNION ALL doesn't (on the RDBMSes that I'm used to).

For your ordering here are two flavors

SELECT * FROM 
(
  select id,name from phonebook WHERE (name='') AND (Tag ='') AND Country= '' order by Points
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM 
(
  select id,name from phonebook WHERE (name='') AND Country= '' order by Points
) DUMMY_ALIAS2

UNION ALL

SELECT * FROM
( 
  select id,name from phonebook WHERE (tag='') AND Country= '' order by Points
) DUMMY_ALIAS3

OR

select id,name, points from phonebook WHERE (name='') AND (Tag ='') AND Country= '' UNION
select id,name, points from phonebook WHERE (name='') AND Country= '' UNION
select id,name, points from phonebook WHERE (tag='') AND Country= '' order by Points;
user2009750
  • 3,169
  • 5
  • 35
  • 58
  • +1 : I'd suggest `UNION ALL`, and possibly an extra column to identify which query generated the row – MatBailie Apr 17 '14 at 10:36
  • @MatBailie if am not sure about difference between `UNION` and `UNION ALL`, I'd really appreciate if you can add details. – user2009750 Apr 17 '14 at 10:38
  • 1
    `UNION` will search through the results and remove duplicates. `UNION ALL` won't even make that attempt. In this case we know in advance the three queries don't have the same results, so `UNION ALL` will remove some redundant CPU effort. Also, `UNION` can cause the ordering to change *(do to the de-duplication)*, where as `UNION ALL` doesn't *(on the RDBMSes that I'm used to)*. – MatBailie Apr 17 '14 at 10:41
  • I can't do order by with Union, I want put order by in each query (not for all) – Rustam Apr 17 '14 at 11:48
  • @Rustam you want order by which column? – user2009750 Apr 17 '14 at 11:50
  • try like this `SELECT * FROM Table WHERE Condition ORDER BY COLUMN UNION SELECT * FROM SOMEOTHERTABLE WHERE CONDITION ORDER BY COLUMN;` – user2009750 Apr 17 '14 at 11:52
  • @pronox I tried this not work. I want order by column called points as i mention in my edit. – Rustam Apr 18 '14 at 09:21
  • @pronox Thank you very much,DUMMY_ALIAS worked – Rustam Apr 20 '14 at 06:44