CODEWITHSUNDEEP
sql
Chris Aplaon
Chris Aplaon

Reputation: 145

Need help for SQL statement s to combine 2 tables

Good Day Everyone,

I kindly need some help for combining to 2 tables using using SQL SELECT ALL

Table accounts

ID| USNM | PSSWRD | DEPT | TYPE | FNAME | LNAME<br>
 1 | user1 | 1234 | fin  | user |  Juan | Cruz<br>
2  | user2 | 5678 | it | sup   |John | Cross

Table adminAccounts

ID | USNM | PSSWRD | DEPT | TYPE | FNAME | LNAME <br>
1  | admin| admin  | all  | admin| Boss  | Owner <br>

Desired Output after joining 2 tables

Table accounts

ID | USNM | PSSWRD | DEPT | TYPE | FNAME | LNAME <br>
1  | user1| 1234   | fin  | user |  Juan | Cruz <br>
2  | user2| 5678   | it   | sup  |  John | Cross <br>
3  | admin| admin  | all  | admin| Boss  | Owner <br>

I am really clueless for this kind of SQL statement because some of the reference online are combining 2 tables with different columns. I would really appreciate the help for providing the SQL statement for Selecting 2 tables for this basic problem.

I am currently working on a inventory system with login capability using account per department.

using (OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM accounts", con))

Upvotes: 0

Views: 63

Answers (4)

Fahmi
Fahmi

Reputation: 37473

you can use union

select ID,USNM ,PSSWRD ,DEPT ,TYPE , FNAME ,LNAME from accounts
union all
select ID,USNM ,PSSWRD ,DEPT ,TYPE , FNAME ,LNAME from adminAccounts

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

UNION [ALL] might seem like the obvious solution, but it doesn't re-set the id column.

So, you need row_number() as well:

select row_number() over (order by which, id) as id,
       USNM, PSSWRD, DEPT, TYPE, FNAME, LNAME
from ((select t1.*, 1 as which
       from table1 t1
      ) union all
      (select t2.*, 2 as which
       from table2 t2
      )
     ) t;

Upvotes: 1

callisto
callisto

Reputation: 5083

Here is an easy way to remember this:

a JOIN adds columns from another table to your query result

a UNION adds rows from another table to query result

enter image description here enter image description here

Upvotes: 0

Michael Villani
Michael Villani

Reputation: 104

Try a Union Select.

SELECT * FROM t1

UNION

SELECT * FROM t2

Union combines the results of two select statements. It is important that they have to have identical columns. See W3 Schools SQL Union

Upvotes: 0

Related Questions