Alternative to SQL UNION

[jasonwcm]

Hi..

Currently I have a sql statement using UNION as follow:

(
SELECT a, b, c
FROM tbl_A, tbl_B
WHERE (some conditions)
)
UNION
(
SELECT a, d AS b, e AS c
FROM tbl_C
WHERE (some conditions)
)

Is there a way to another way to achieve the same result
without using the UNION cos I also want to include some SQL search and sorting statements which the current situation doesn't allow..??

Any help is greatly appreciated.

Cheers,

Jason

 

[SpiderBear6]

You can make that union statement a view. Then you can use the view as if it were a table.



--------------------------------------
"We are star-stuff. We are the universe made manifest trying to figure itself out."
-- Delenn in Babylon 5 - "A Distant Star"

 

[jasonwcm]

Thanks for the quick reply..

I haven't create a view before. It would be great if you can provide a example or point me to some URLs perhaps.

Cheers.

 

[essa2000]

Hi jason ,

You can perform your task in one query either using Derived Tables (the tables which are created in From Clause)

SELECT a, b, c
FROM tbl_A, tbl_B ,
(SELECT a, d AS b, e AS c
FROM tbl_C
WHERE (some conditions) ) As DerivedTable
WHERE (some conditions)

See into Derived Table. If you give me your real tables names and scenario the nI can get back to you with real time solution. You can see help of Derived Table with Examples in BOL.

Thanks




Muhammad Essa Mughal
Software Engineer
iLogic Inc.

 

[SonOfEmidec1100]

You could also use a stored procedure to create a table data type, populate it, then do your things. example

CREATE PROCEDURE dbo.GetMyData AS

DECLARE @mytable table (
a varchar (50) NULL ,
b varchar (50) NULL ,
c varchar (50) NULL )

SET NOCOUNT ON

INSERT into @MyTable
SELECT a, b, c FROM tbl_A, tbl_B
WHERE (some conditions)

INSERT into @MyTable
SELECT a, d AS b, e AS c
FROM tbl_C
WHERE (some conditions)

SET NOCOUNT OFF
--select rows to return
SELECT * FROM @mytable where a= 2 order by c

 

[rochana]

Hi

You can also create a temporary table with the same query you have and manipulate it as you wish.

SELECT a, b, c into
NEW_TEMP_TABLE
from 
(
  SELECT a, b, c
  FROM tbl_A, tbl_B
  WHERE (some conditions)
UNION
  SELECT a, d AS b, e AS c
  FROM tbl_C
  WHERE (some conditions)
) s1

rochana