CODEWITHSUNDEEP
phpmysqlsqlsql-server
Julius ShadowAspect Flimmel
Julius ShadowAspect Flimmel

Reputation: 153

SQL select from table and get table name

I have like three tables which have some same columns like ID, Name, Image, ... I am selecting from them using this code

(SELECT ID, Name FROM `creatures` WHERE ... )
UNION
(SELECT ID, Name FROM `items` ...)
UNION
( ... FROM `anothertable` ... )

Im doing this in php which creates a row to a table for each result. Now the problem is, I need to know from which table the row was taken. Im new to SQL and I dont know if it even can be done somehow. I though about something like:

SELECT ID, Name, TableName FROM ...

Is it possible?

Upvotes: 1

Views: 2294

Answers (4)

Binyamin Melepat
Binyamin Melepat

Reputation: 59

You can simply hard code in the sql as below

(SELECT ID, Name,'creatures' as TableName FROM `creatures` WHERE ... )
UNION
(SELECT ID, Name,'items' as TableName FROM `items` ...)
UNION
( ... FROM `anothertable` ... )

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18737

Try this:

(SELECT ID, Name, 'creatures' as TableName FROM `creatures` WHERE ... )
UNION ALL
(SELECT ID, Name, 'items' as TableName FROM `items` ...)
UNION All
( SELECT ID, Name, 'anothertable' as TableName FROM `anothertable` ... )

Sample result:

ID   Name    TableName
1    Name1   creatures
2    Name2   creatures
1    Name3   items
1    Name5   anothertable

Upvotes: 1

Mureinik
Mureinik

Reputation: 311028

You can just add the table name as a string literal to each query:

(SELECT 'creatures' AS table_name, ID, Name FROM `creatures` WHERE ... )
UNION
(SELECT 'items' AS table_name, ID, Name FROM `items` ...)
UNION
( ... FROM `anothertable` ... )

EDIT: Notice that since you know that two rows from different tables cannot be equal, you can now use union all instead of union, which may give you a slight performance boost.

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

You can just add a column indiciating the table name to the selects;

(SELECT 'creatures' AS tablename, ID, Name FROM `creatures` WHERE ... )
UNION
(SELECT 'items' AS tablename, ID, Name FROM `items` ...)
UNION
( ... FROM `anothertable` ... )

A side note, since you want all rows, using UNION ALL is faster since it does not need to perform a DISTINCT to eliminate duplicates.

Upvotes: 3

Related Questions