CODEWITHSUNDEEP
sqldatabase
Gerry
Gerry

Reputation: 127

Include table name in column from select wildcard sql

Is it possible to include table name in the returned column if I use wildcard to select all columns from tables?

To explain it further. Suppose I want to join two tables and both tables have the column name “name” and many other columns. I want to use wildcard to select all columns and not explicitly specifying each column name in the select.

Select *
From 
TableA a,
TableB b
Where 
a.id = b.id

Instead of seeing two column with same name "name", could I write a sql to return one column name as "a.name" (or TableA.name) and one as "b.name"(or TableB.name) without explicitly putting the column name in select?

I would prefer a solution for mssql but other database could be a reference too.

Thanks!

Upvotes: 4

Views: 2221

Answers (3)

JohnH
JohnH

Reputation: 2133

Try ...

SELECT 'TableA' AS 'Table', A.* FROM TableA A
WHERE A.id IN (SELECT id FROM TableB)
UNION
SELECT 'TableB' AS 'Table', B.* FROM TableB B
WHERE B.id IN (SELECT id FROM TableA)
ORDER BY id, [Table]

Upvotes: -1

Anthony R Gray
Anthony R Gray

Reputation: 93

You are basically joining two tables on the ID field, so you will only see one column labeled "ID", not two, because you are asking to see only those records where the ID is the same in table a and table b: they share the same id.

Upvotes: 1

Stoleg
Stoleg

Reputation: 9320

You can use select a.*, ' ', b.* from T1 a, T2 b to make it more visible where columns from T1 end and columns from T2 begin.

Upvotes: 3

Related Questions