CODEWITHSUNDEEP
sqlms-access
Alex
Alex

Reputation: 53

SQL Join 2 tables with different columns

I am trying to create a single login function for usernames and password that exist across 2 tables. Currently I have

SELECT * FROM CUSTOMER WHERE username = usernameIN AND password = passwordIN
SELECT * FROM STAFF WHERE username = usernameIN AND password = passwordIN

If there is a match ill log the user in.

Is there a query to join these 2 tables so I can run 1 SQL query on them.

Input tables

CUSTOMER
username,password,firstname,lastname,addressline1,town

andys,pass123,andy,smith,123 fake st,fake town

STAFF
username,password,firstname,lastname,position,salary

davidq,pass321,david,queen,manager,10000

Expected Output from SQL Query

OUTPUT TABLE   
username,password,firstname,lastname,addressline1,town,position,salary

andys,pass123,andy,smith,123 fake st,fake town,null,null
davidq,pass321,david,queen,null,null,manager,10000

Upvotes: 0

Views: 100

Answers (4)

zarruq
zarruq

Reputation: 2465

You need UNION ALL.

SELECT username,
       password1,
       firstname,
       lastname,
       addressline1,
       town,
       NULL AS manager,
       NULL AS salary
FROM customer
UNION ALL
SELECT username,
       password1,
       firstname,
       lastname,
       NULL,
       NULL,
       POSITION,
       salary
FROM staff;

You can add WHERE clauses to your individual queries accordingly.

More over if you want to avoid duplicate rows, use UNION

Result:

username    password1   firstname   lastname    addressline1    town            manager     salary
--------------------------------------------------------------------------------------------------
andys       pass123     andy        smith       123 fake st     fake town       NULL        NULL
davidq      pass321     david       queen       NULL            NULL            manager     10000

DEMO

Upvotes: 4

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

You can join both the table using UNION like below.

SELECT username, password, firstname, lastname, addressline1, town, position, salary FROM 
(SELECT username, password, firstname, lastname, addressline1, town, null position, null salary FROM CUSTOMER
UNION ALL
SELECT username, password, firstname, lastname, null addressline1, null town, position,salary FROM STAFF) AS t
WHERE t.username = 'username' AND t.password = 'password'

Upvotes: 1

Schmocken
Schmocken

Reputation: 613

SELECT 
  username,password,firstname,lastname,addressline1,town, null as position, null as salary
FROM
  CUSTOMER  
WHERE username = usernameIN AND password = passwordIN  
UNION ALL  
SELECT  
  username,password,firstname,lastname, null, null, position,salary  
FROM  
  STAFF  
WHERE username = usernameIN AND password = passwordIN

You'll have to add aliases

Upvotes: 1

Sorix
Sorix

Reputation: 918

Use UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Upvotes: 0

Related Questions