0

I have 3 tables and each one with different structure and number of different columns, but the first 4 columns are the same in all tables. For instance

Table 1

  • Type
  • id1
  • id2
  • Period
  • FirstName
  • LastName
  • Value

table2

  • Type
  • id1
  • id2
  • Period
  • Hours
  • Days

table3

  • Type
  • id1
  • id2
  • Period
  • Sex
  • Address

I currently run a stored procedure

@Numeric period (6,0)

begin
select * from table1 where @ Period = 202109;
select * from table2 where @ Period = 202109;
select * from table3 where @ Period = 202109
end

... and I get the following results:

Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Sex | Address
Type | id1 | id2 | Period | Sex | Address
Type | id1 | id2 | Period | Sex | Address

but what I need is the result to be like this:

Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Sex | Address
Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Sex | Address
Type | id1 | id2 | Period | FirstName | LastName | Value
Type | id1 | id2 | Period | Hours | Days
Type | id1 | id2 | Period | Sex | Address

The fields Type, id1, id2 and Period are the keys and the search criteria.

4
  • 1
    Are you using PostgreSQL or Microsoft SQL Server?...please update your post's tags accordingly.
    – J.D.
    Oct 4 '21 at 6:08
  • Hi, welcome to the community. Please have a look at: Minimal, Complete, and Verifiable Example
    – McNets
    Oct 4 '21 at 6:50
  • I removed the postgresql tag as the code is clearly for SQL Server Oct 4 '21 at 7:19
  • Hi, and welcome to dba.se! Please go to dbfiddle.uk and construct your tables (preferably with some sample data) - help us to help you! Oct 4 '21 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.