Mysql Joins - How to know which row is retrieved from which table...?

Question

Hi everyone...

Consider i am using join on three tables to get a desired result-set...

Now in that result set, is there anyway that i can find out which row comes from which table...?

Update : I see that i have phrased the question rather wrongly.... As pointed in one of the answers below, a result-set returned by join may will contain a row made up columns from multiple talbes...

So the question should actually be "Consider i am using union on three tables to get a desired result-set..."

Answer 1

If you are asking this question, then your database is probably not structured correctly. (correctly being a subjective term).

A proper SQL query on a normalized database should not depend, nor be concerned with, where the data comes from.

Each row would be a combination of all tables, with null values being inserted in columns for left/right/outer joins which do not match the joining criteria. You could perhaps test if a column (from a particular table) is null, and derive from that that the non-null values must originate from the opposite table(s).

Then again, if you were actually performing an UNION, as Marcelo suggested, you would have to look at ancillary columns to determine the source of the data, as that information is lost in the combination.

Answer 2

You can add a table identifier column to each:

select 'A' as table_name, col1, col2 from A
union
select 'B' as table_name, col1, col2 from B
union
...

This returns a single result set which is handled by your application as any ordinary select statement:

while ( rows available ) {
  row = fetchrow
  if row.table_name == 'A' then 
     do special handling for table A
  else if row.table_name == 'B' then
     do special handling for table B
  else if ...

}

The actual syntax is dependent on the language you are using, but most procedural languages follow the scheme above.