Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mimic
Starting Member
18 Posts |
 Posted - 2007-10-19 : 03:41:46
|
| I need to write something like:select ..., b.name, b.name from a,b where b.id = a.id1 and b.id =a.id2 so i have two fields b.name from table b but they match different column from second table. The fields id1 and id2 do not have the same values, but in my select there is.can you help me? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-19 : 04:05:21
|
With Microsoft SQL Server it is hard to have same column name twice or more in same table or view.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-19 : 04:32:51
|
| I'd sure like to see the output ofSELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'b' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-19 : 04:43:26
|
quote:
Originally posted by mimic
I need to write something like:select ..., b.name, b.name from a,b where b.id = a.id1 and b.id =a.id2 so i have two fields b.name from table b but they match different column from second table. The fields id1 and id2 do not have the same values, but in my select there is.can you help me?
Why do you want to have same columns twice in a single select?MadhivananFailing to plan is Planning to fail |
|
|
|
mimic
Starting Member
18 Posts |
Posted - 2007-10-19 : 05:16:09
|
| - first b.name is the name of let say creator of the file- second b.name is the name of let say the person who made the last change to the file (those two are not the same) in the first table (a)those fileds are just id of the worker,the actual names are store in the workers table (b)so i have: select ...,b.name(teh name of creator), b.name(changer of file) from a,b where b.id = a.id(first b.name) and b.id = a.id(second b.name) .. and i'm stuck here! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-19 : 05:33:39
|
| What doesSELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'b'show? |
|
|
|
mimic
Starting Member
18 Posts |
Posted - 2007-10-19 : 05:44:58
|
| i solved it, select ...,b.name, b1.name from a,b where b.id = a.id(first b.name) and b1.id = a.id(second b.name)but now i have another problem. in the a table i have same id =0, but in workers table (id starts with 1) so the records for value 0 are not displayed in my select. So i would need something like:select ...,b.name, b1.name from a,b where if a.id= 0 b.name ='unknown'else b.id = a.id(first b.name) and b1.id = a.id(second b.name)but this is not right. an anyone write how to use If in this case? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-19 : 06:16:09
|
| "i solved it"You did?"select ...,b.name, b1.name from a,b where b.id = a.id(first b.name) and b1.id = a.id(second b.name)"What does B1 refer to then?New problem:Use a JOIN instead of a list of columns.Then you can use a LEFT OUTER JOIN for the B table, and thus you will get rows from A table even if there is no matching B row, and then you can doSELECT ..., COALESCE(b.name, 'unknown') as [Name]Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-19 : 06:36:10
|
quote:
Originally posted by mimic
i solved it, select ...,b.name, b1.name from a,b where b.id = a.id(first b.name) and b1.id = a.id(second b.name)but now i have another problem. in the a table i have same id =0, but in workers table (id starts with 1) so the records for value 0 are not displayed in my select. So i would need something like:select ...,b.name, b1.name from a,b where if a.id= 0 b.name ='unknown'else b.id = a.id(first b.name) and b1.id = a.id(second b.name)but this is not right. an anyone write how to use If in this case?
Are you using SQL Server?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
problem