CODEWITHSUNDEEP
sqlsqlite
Yahia
Yahia

Reputation: 691

make 2 select statements from 2 tables and then join the result with third table

I have a database with three tables:

Table 1

CREATE TABLE "A" ( "id" INTEGER PRIMARY KEY  NOT NULL 
                  ,"cat_id" INTEGER NOT NULL
                  ,"date" DATETIME NOT NULL
                  ,"amount" VARCHAR NOT NULL  DEFAULT (0.0)
                  ,"notes" VARCHAR)

Table 2:

CREATE TABLE "B" ( "id" INTEGER PRIMARY KEY  NOT NULL
                  ,"cat_id" INTEGER NOT NULL
                  ,"date" DATETIME NOT NULL
                  ,"amount" VARCHAR NOT NULL  DEFAULT (0.0)
                  ,"notes" VARCHAR)

Table 3:

CREATE TABLE "c" ( "id" INTEGER PRIMARY KEY  NOT NULL
                  ,"parent_id" INTEGER NOT NULL  DEFAULT (0)
                  ,"name" VARCHAR NOT NULL
                  ,"position" INTEGER NOT NULL
                  ,"importance" INTEGER NOT NULL  DEFAULT (0)
                  ,"transaction_type" VARCHAR NOT NULL  DEFAULT expenses
                  ,"icon" VARCHAR NOT NULL  DEFAULT default
                  ,"budget" INTEGER NOT NULL  DEFAULT 0
                  ,"status" INTEGER NOT NULL  DEFAULT 1)

I use this statement to select from the tables A and B: 

SELECT * FROM A
UNION ALL
SELECT * FROM B 
ORDER BY date

I want to join the result of this select statement with the third table C with: 

RIGHT JOIN C on A.cat_id = C.id
RIGHT JOIN C on B.cat_id = C.id

How can i do this?!

Upvotes: 0

Views: 1118

Answers (2)

Boris Vasilyev
Boris Vasilyev

Reputation: 243

Also you can join then union tables) Like distributive property in math:

select *
from (
      (SELECT * FROM A right join C
        on ab.cat_id = c.id) 
      UNION ALL
      (SELECT * FROM B right join C
        on ab.cat_id = c.id)
     ) ab 
ORDER BY date

Hovewer, this is not the best way, due to double join.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do it just by combining the elements of the SQL language:

select ab.*, c.*
from ((SELECT *
       FROM A
      ) UNION ALL
      (SELECT *
       FROM B
      )
     ) ab right join
     C
     on ab.cat_id = c.id
ORDER BY date

This makes the union all a subquery and then does the join from there.

Upvotes: 3

Related Questions