CODEWITHSUNDEEP
sql-serverjoinunion
vnkotak
vnkotak

Reputation: 127

SQL Server - Retrieve data from separate tables without using UNION clause

I have a problem and not sure if there is even a possible solution, however still thought to raise it in this forum as there are some masters here who might be able to provide some solution :)

I have two tables - EMAIL_Request and SMS_Request. Sample data model provided below.

Email_Request

Seq Cust_ID  Email_ID           Sent
1    123     [email protected]       Yes
2    234     [email protected]        No

SMS_Request

Seq Cust_ID  MobileNo           Sent
1    345     1234567890          Yes
2    456     2345678900          Error

Report

I have to show output of both the tables in one single report with mode. Sample expected report.

Mode    Cust_Id        Sent_To            Sent
EMAIL     123        [email protected]        Yes
EMAIL     234        [email protected]         No
SMS       345        1234567890            Yes
SMS       456        2345678900            Error

PROBLEM:

We have in-house built reporting screen using Java-Spring/Hibernate where user can see the report. We just have to add Query in database and framework will handle the rest.

Users can dynamically add criteria in the report as well to search by Mode or Sent (status). There is a search facility on the screen to just retrieve Email records or SMS records. Also another criteria by which users can search by Sent (status).

Currently the in-house built framework simply appends the where criteria to whole query depending on user entry. Hence, I cannot use UNION clause to retrieve the report.

So, what is the best option to write a SQL query where I can retrieve the data as expected, probably using JOIN clause, but here I don't have any field that I can join between two tables and as already highlighted I cannot use UNION clause.

PS: Seq of both the tables are just their primary keys, so cannot be used to join.

Upvotes: 0

Views: 105

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

If the reason you think you can't use UNION is because you have to write a query with only one WHERE clause, then use a CTE:

WITH CTE AS (
Query1
UNION ALL
Query2
)
SELECT * FROM CTE
WHERE...

Upvotes: 3

Mukund
Mukund

Reputation: 1689

I think below query should solve your problem.

select * from 
(
  select 'EMAIL' as Mode, cust_id, email_id as sent_to, Sent
  from EMAIL_Request

  union all

  select 'SMS' as Mode, cust_id, Mob_no as sent_to, Sent
  from SMS_Request
) as temp
where temp..... = ...

http://www.sqlfiddle.com/#!3/143d9/2

Upvotes: 1

Related Questions