CODEWITHSUNDEEP
phpmysqlsql-server
Taylor Foster
Taylor Foster

Reputation: 1215

Searching multiple SQL tables for certain row then return row as well as table name

I am somewhat new to SQL so I am still trying to learn some of the simple things.

Right now I have multiple tables for different levels of access on my application (Customers, Representatives, Managers).

A username CANNOT be shared between these tables. What I am getting held up at is trying to select the users row based on the username.

I would easily know how to do this if there were just one table for users but I am not really sure how to search multiple tables at the same time.

Also if a username is found, is there a way to return the name of the table that it found that username in?

Thank you so much!

Upvotes: 0

Views: 35

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94672

A simple UNION might be all you need

SELECT 'table1' as tab, c1, c2, c3 FROM table1 WHERE uid='xxx'
UNION
SELECT 'table2' as tab, c1, c2, c3 FROM table2 WHERE uid='xxx'
UNION
SELECT 'table3' as tab, c1, c2, c3 FROM table3 WHERE uid='xxx'

As you will only get one row returned as user xxx can only exist in one of the tables you get all the columns you are interested in plus the first column containing your table name

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425623

Assuming all tables' layouts are the same:

SELECT  *
FROM    (
        SELECT  'customers' tn, *
        FROM    customers
        UNION ALL
        SELECT  'managers' tn, *
        FROM    managers
        UNION ALL
        SELECT  'representatives' tn, *
        FROM    representatives
        ) q
WHERE   username = @myusername

Upvotes: 0

Related Questions