2 sql statmetns 1 table

Question

Post reply

2 sql statmetns 1 table

ibrahim_sh_87

SSC-Addicted

Points: 451
More actions
April 7, 2009 at 3:40 am

#95185

hello all,
i am trying to display on my web application the result of 2 deferent sql select statements in 1 table(grid).. the two select statments produce identical columns with the same column names...that is what i did exactly
(select col1,col2 from table1)full join (select col1,col2 from table2)
of course my 2 select statements are not as simple as shown they contain joins and collations and order by 's but here i showed this for simplification purposes ..when i ran this statement i got Incorrect syntax near the keyword 'full' thanks in advance for youre help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Try this:

SELCT Col1, col2, col3 from table1

UNION ALL

SELCT Col1, col2, col3 from table2

[font="Verdana"]Markus Bohse[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The query you're posted isn't viable. I reckon Markus has figured it out, but if not, here's some sample data which you could use to show the output you expect from your query:

DROP TABLE #table1

CREATE TABLE #table1 (col1 CHAR(1), col2 CHAR(1), col3 CHAR(1), col4 CHAR(1), col5 CHAR(1))

INSERT INTO #table1 (col1, col2, col3, col4, col5)

SELECT 'X', 'Y', '1', '3', 'A' UNION ALL

SELECT 'X', 'Y', '2', '4', 'A' UNION ALL

SELECT 'X', 'Y', '5', '6', 'Z' UNION ALL

SELECT 'C', 'D', '2', '4', 'Z' UNION ALL

SELECT 'C', 'D', '1', '3', 'A' UNION ALL

SELECT 'C', 'D', '5', '6', 'A' UNION ALL

SELECT 'E', 'F', '3', '5', 'Z'

--

DROP TABLE #table2

CREATE TABLE #table2 (col1 CHAR(1), col2 CHAR(1), col3 CHAR(1), col4 CHAR(1), col5 CHAR(1))

INSERT INTO #table2 (col1, col2, col3, col4, col5)

SELECT 'A', 'B', '1', '3', 'b' UNION ALL

SELECT 'A', 'B', '2', '4', 'b' UNION ALL

SELECT 'A', 'B', '3', '6', 'y' UNION ALL

SELECT 'E', 'F', '4', '4', 'y' UNION ALL

SELECT 'E', 'F', '5', '3', 'b' UNION ALL

SELECT 'E', 'F', '6', '6', 'b' UNION ALL

SELECT 'G', 'H', '7', '5', 'y'

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ibrahim_sh_87 SSC-Addicted Points: 451 More actions · Answer 3

no actually i think it solved my problem, the data i am showing is combining 2 tables one is remote and one is local...after adding the union command i got some collation errors which i solved using collate command then i got this error

OLE DB provider "SQLNCLI" for linked server "ERP2" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16937, Level 16, State 1, Procedure _SKB_Sites_LstVw, Line 1

A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor.

if i am not wrong this is a authority error so i am going to the remote table DBA in order to help me with it thank you guys for your quick response