Joining Tables With Table Name As Column 2

[MarkZK]

Hi All,

I'm trying to join data from three tables into one, but also create a column to hold the name (or in my case set As Path) of the table that the data came from.

Maybe an example will help explain this better....


tbl_one
1.jpg | 2010-06-08
2.jpg | 2010-06-06

tbl_two
1.jpg | 2010-06-06
2.jpg | 2010-06-07

tbl_three
1.jpg | 2010-06-07
2.jpg | 2010-06-08


the result (order by #date# desc) being...

#name# #date# #path#
1.jpg | 2010-06-08 | folder1
2.jpg | 2010-06-08 | folder3
1.jpg | 2010-06-07 | folder3
2.jpg | 2010-06-07 | folder2
1.jpg | 2010-06-06 | folder2
2.jpg | 2010-06-06 | folder1

note: folder1 is data from tbl_one, folder2 is data from tbl_two.... so on....


If anyone has any ideas, that'd be great.

Thanks for reading.

 

[gmmastros]

Sounds like a job for Union All. Something like this:

Select Name, Date, 'Folder1' As Path
From   tbl_one

Union All

Select Name, Date, 'Folder2'
From   tbl_two

Union all

Select Name, Date, 'Folder3'
From   tbl_three

Order BY Date DESC, Name

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

And there I was looking around for something similar to DB_NAME() only for a table .


That code worked perfectly though, as always.

Thanks George!.

 

[peac3]

Sounds like a job for Union All.

Why not union, to avoid duplication?

 

[gmmastros]

Peac3,

you're right that union will remove duplicates, but if the data is naturally distinct anyway, you should use union all because it will perform faster. It's up to the original poster to determine which one to use.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom