Solved

SQL query

Posted on 2014-01-04
7
545 Views
Last Modified: 2014-01-07
Hi,

I am fetching data from three different tables and column names are different in all three tables and there is no relationship between these three tables.

/*Report1*/
Select a1,a2,a3 from temp1
/*Report2*/
Select b1,b2,b3,b4,b5 from temp2
/*Report3*/
Select c1,c2 from temp3

At the end of query I have to display all three tables results in single output using sql query.
For ex:
a1	a2	a3	b1	b2	b3	b4	b5	c1	c2

Open in new window


Please advise how to achieve above requirement using sql query.

Thanks in advance.
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39756911
If there is no logical relationship between the 3 tables then its not possible, how else are you going to determine which row data from temp1 is joined with which row data from temp2 and temp3
0
 

Author Comment

by:sqldba2013
ID: 39756971
Hi Paul Jackson,

I don't want to add any condition/join, Just I want to merge all three tables results and need to display results in single output using normal select statement.

For ex,
report 1 query returns 10 rows,
report 2 query returns 80 rows,
report 3 query returns 2 rows

In final output, I need 92 rows with all 3 table columns headers.

All 3 tables are temp tables not physical tables.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39756980
Ok then just use UNION operator :

Select a1,a2,a3 from temp1
UNION
Select b1,b2,b3,b4,b5 from temp2
UNION
Select c1,c2 from temp3
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:sqldba2013
ID: 39757084
Thanks for your suggestion.

I can't use UNION or UNION ALL because number of columns count is different in all tables.

I got below error.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Any other suggestions?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39757489
I can't use UNION or UNION ALL because number of columns count is different in all tables.
You have to use exactly the same number and data type for all the columns.  So assuming the data types are the same then:
/*Report1*/
SELECT  a1,
        a2,
        a3,
        NULL a4,
        NULL a5
FROM    temp1
/*Report2*/
UNION ALL
SELECT  b1,
        b2,
        b3,
        b4,
        b5
FROM    temp2
/*Report3*/
UNION ALL
SELECT  c1,
        c2,
        NULL,
        NULL,
        NULL
FROM    temp3
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 39758494
In your question you specify a requirement for a row like this:
a1	a2	a3	b1	b2	b3	b4	b5	c1	c2

Open in new window

If that column arrangement is important then you would extend the use of  NULL like so:
/*Report1*/
SELECT
      a1
    , a2
    , a3
    , NULL AS b1
    , NULL AS b2
    , NULL AS b3
    , NULL AS b4
    , NULL AS b5
    , NULL AS c1
    , NULL AS c2
FROM temp1
/*Report2*/
UNION ALL
      SELECT
            NULL AS a1
          , NULL AS a2
          , NULL AS a3
          , b1
          , b2
          , b3
          , b4
          , b5
          , NULL AS c1
          , NULL AS c2
      FROM temp2
/*Report3*/
UNION ALL
      SELECT
            NULL AS a1
          , NULL AS a2
          , NULL AS a3
          , NULL AS b1
          , NULL AS b2
          , NULL AS b3
          , NULL AS b4
          , NULL AS b5
          , c1
          , c2
      FROM temp3
;

Open in new window

0
 

Author Closing Comment

by:sqldba2013
ID: 39764079
--
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question