CODEWITHSUNDEEP
mysqlview
Ayush
Ayush

Reputation: 179

how to create a view on two different tables containing the same columns with different data

I have two table account (contains the active customer information ) and account_draft (contains the draft information)

Account

id  |description       |currency_code| status |
----|------------------|-------------|--------|
1     AccountTransfer       SGD         Active
2     CTS                   SGD         Active


Account-draft
id  |description       |currency_code| status |
----|------------------|-------------|--------|
1     AccountTransfer       SGD         Approved
2       CTS                 SGD         pending
3       payment             SGD         deleted

how to create view contains the data of both the tables (active and draft) and identify the each record based on status. Active tables contains the records with status(active) where as draft table contains the record with status (draft,approved,pending,hold)

View looks like below,

View_account

id  |description       |currency_code| status | Type      | 
----|------------------|-------------|--------|-----------|
1     AccountTransfer       SGD         Active     active
1     AccountTransfer       SGD         Approved   draft
2     CTS                   SGD         Active     active
2     CTS                   SGD         pending    draft
3     payment               SGD         deleted    draft

pls help.

Upvotes: 0

Views: 511

Answers (1)

Nick
Nick

Reputation: 147266

You can use a UNION query, adding an extra column Type based on which table you read data from:

CREATE VIEW alldata AS
SELECT id, description, currency_code, status, 'active' AS Type
FROM Account
UNION ALL
SELECT id, description, currency_code, status, 'draft'
FROM `Account-draft`
ORDER BY id, status

Then you can

SELECT *
FROM alldata

Output:

id  description         currency_code   status      Type
1   AccountTransfer     SGD             Active      active
1   AccountTransfer     SGD             Approved    draft
2   CTS                 SGD             Active      active
2   CTS                 SGD             pending     draft
3   payment             SGD             deleted     draft

Demo on SQLFiddle

Upvotes: 1

Related Questions