Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

SQL query

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
sqldba2013
Asked:
sqldba2013
2 Solutions
 
Paul JacksonSoftware EngineerCommented:
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
 
sqldba2013Author Commented:
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
 
Paul JacksonSoftware EngineerCommented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sqldba2013Author Commented:
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
 
Anthony PerkinsCommented:
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
 
PaulCommented:
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
 
sqldba2013Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now