CODEWITHSUNDEEP
mysqlselectunion
Joris Ooms
Joris Ooms

Reputation: 12038

Selecting across multiple tables with UNION

I reworked my database from one user table to multiple user tables (divided per role): tblStudents, tblTeachers, tblAdmin

When logging in, I didn't want to run three queries to check if the user exists somewhere in my DB. So what I did was put together the following query with union

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t

This selects the fields that are the same across all tables and outputs the results nicely for me.

So, I decided to try this and for some reason, my login form wouldn't work. For my login form, I added a where clause which checks for the email address. I ran the query in my database app and surprisingly, when I do for example where email = "[email protected]" (this email exists in my database tblAdmin), it also selects a record from my students table.

With the where clause: 

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[email protected]"

The records both have id = 1 but I don't understand why it would select the student record when I'm filtering on the admin email address. Why is this? Can someone explain and provide me with a better solution to this problem? I basically have one login form and need to select across multiple tables to check if the user exists in my db.

Upvotes: 15

Views: 93406

Answers (2)

sajjad parmar
sajjad parmar

Reputation: 11

SELECT
    R.co_C,
    company3.Statuss
FROM
    (
        SELECT
            company1.co_C
        FROM
            company1
        UNION ALL
            SELECT
                company2.co_C
            FROM
                company2
    ) AS R
LEFT JOIN company3 ON R.co_C = company3.co_A;

Upvotes: 1

El Yobo
El Yobo

Reputation: 14946

Thanks for updating the query; now we can see that the WHERE condition is only applied to the last UNIONed query. You need to either add that WHERE clause to each query, or wrap it as a subselect and apply the WHERE clause to that.

select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
where email = "[email protected]"
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
where email = "[email protected]"
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
where email = "[email protected]"

or

SELECT * FROM (
select s.id as id, s.email as email, s.password as password, s.role as role from tblStudents s
union
select a.id as id, a.email as email, a.password as password, a.role as role from tblAdmin a
union
select t.id as id, t.email as email, t.password as password, t.role as role from tblTeachers t
) foo where email = "[email protected]"

Upvotes: 22

Related Questions