October 7, 2005 at 10:56 am
I have a question on how to return only some records from my existing
query (shown below). I want to return only records that occur in both
sides of the union AND have the same tablename. (Maybe thier is an
easier way than starting with the union, which you may suggest).
So, for example, imagine the query to return the following records
Union 1 tax_info tableName1
Union 1 pretax tableName1
Union 1 tax_info tableName2
Union 2 sales_info tableName1
Union 2 sales_info tableName3
I would want to construct some type of additional criteria that would
return only
tax_info tableName1
as the table 'tableName1' is the only table to be in both
Union 2.
Thanks
SELECT
'Union 1' as ObjectType,
A.name AS ColumnName,
B.name AS TableName
FROM
dbo.syscolumns A, dbo.sysobjects B
WHERE
A.id = B.id
AND
(A.name like '%tax%' and A.name like '%sale%') and (B.xtype = 'V')
SELECT
'Union 2' as ObjectType,
A.name AS ColumnName,
B.name AS TableName
FROM
dbo.syscolumns A, dbo.sysobjects B
WHERE
A.id = B.id
AND
A.name like '%Sales%'
and
(B.xtype = 'V')
October 7, 2005 at 12:54 pm
Let me see if I understand this correctly. You have multiple '%sale%' tables that can contain the column, (I still always want to say field) tax_info.
You want to only choose one of the '%sale%' tables that have that column?
I wasn't born stupid - I had to study.
October 7, 2005 at 4:03 pm
This may work, if I understand your question. I think you're looking for a list of views and %tax%sale% (or %sale%tax%) columns that also contain a %sales% column.
select
ColumnName, TableName
from (
select so.id, so.name as TableName, sc.name as ColumnName
from sysobjects so inner join syscolumns sc on so.id = sc.id
where so.xtype = 'V' and sc.name like '%tax%' and sc.name like '%sale%'
) a
where id in (
select so.id from sysobjects so inner join syscolumns sc on so.id = sc.id
where so.xtype = 'V' and sc.name like '%sales%')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply