| Author |
 Topic  |
|
|
uv2944875
Starting Member
5 Posts |
 Posted - 11/27/2012 : 13:59:09
|
Suppose I have table T(A,B). A tuple t in this table t = (a,b) means that column b is in table a, i.e., there exists a table a in the database, with the schema a(b,...).
How can I query column b in table a, for all the tuples t in T, when T itself is the result of a select?
Thanks
Update. A small example.
Suppose I have table x(y) with tuples {1,2,3} and table c(d,e) with tuples{(4,100),(5,100)}. In a select query (with some filters) I obtain table T(a,b) with tuples {(x,y),(c,d)}. I'd like to query this table T (in the same select where I obtained T) to get a final table with schema(z) and tuples {(1),(2),(3),(4),(5)}.
Assume same column type everywhere, no worries about that. |
Edited by - uv2944875 on 11/27/2012 14:06:59
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 11/27/2012 : 14:19:39
|
I don't speak college professor, but you can query a query, it's called a derived table, if this is what you're after.
SELECT <some columns from a>,<some columns from b>
FROM
( select <some other stuuf>
from someTable
) a
INNER JOIN
( select <some other stuuf>
from someOtherTable
) b
ON a.someColumn = b.SomeColumn
jim
Everyday I learn something that somebody else already knew |
 |
|
|
uv2944875
Starting Member
5 Posts |
Posted - 11/28/2012 : 05:29:45
|
| I don't think this is it. The content of a tuple, i.e., the values for columns a and b in table T, is now used both in the SELECT and FROM clauses of a select query. I think it's smth to do with dynamic sql. |
|
|
|
Mike Jackson
Starting Member
37 Posts |
Posted - 11/28/2012 : 08:34:53
|
Perhaps you could supply us a data sample, an example of how it is normally solved and expected result.
Mike |
|
|
|
Elizabeth B. Darcy
Starting Member
United Kingdom
39 Posts |
Posted - 11/28/2012 : 09:03:44
|
The only way to do this in Transact SQL is using dynamic queries. For the example you posted, you would construct the query string like in the example below:CREATE TABLE #t(tbl VARCHAR(32), col VARCHAR(32));
INSERT INTO #t VALUES ('x','y'),('c','d');
DECLARE @sql NVARCHAR(4000);
SET @sql =
STUFF(
(
SELECT
' UNION ALL SELECT ' + QUOTENAME(col) + ' FROM ' + QUOTENAME(tbl)
FROM
#t
FOR XML PATH('')
) ,1,11,'')
EXEC( @sql );
DROP TABLE #t;
________________________________________
-- Yes, I am indeed a fictional character. |
|
|
|
uv2944875
Starting Member
5 Posts |
Posted - 11/29/2012 : 04:40:17
|
| Thanks Elizabeth B. Darcy, it is what I was looking for :). |
|
|
|
Elizabeth B. Darcy
Starting Member
United Kingdom
39 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 12/04/2012 : 06:16:08
|
But why do you have a design like this?
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
some dynamic sql
