SQL Question - Unions -- sql server new users

sql server new users : SQL Question - Unions

Gary Rynearson
10/7/2005 11:05:23 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 1 and Union
2.

Thanks
Gary

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')

UNION
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')

Keith Kratochvil
10/7/2005 11:29:18 AM

Multiple columns appear from tableName1. Wouldn't your expected results
look like this:
tax_info tableName1
sales_info tableName1

--
Keith

[quoted text, click to view]

Gary Rynearson
10/7/2005 12:52:32 PM

Keith

Ahhh ... Yes you are right. And now that you mention it, I would actually
want as a return either just

tableName1

Do you know of a way to get this?

Gary

Yes, you are right. I would be happy with a query that would return just
what you indicated,
I would like only the distinct values to be retrned, so in this case, my
desired return would be just tableName1.

[quoted text, click to view]

Keith Kratochvil
10/7/2005 2:27:23 PM

I modified the criteria slightly so that it would work with the data that I
have within my system tables.

This version retrieves data from both tables:

SELECT
'Union 1' as ObjectType,
A.name AS ColumnName,
B.name AS TableName
--select *
FROM
dbo.syscolumns A, dbo.sysobjects B
WHERE
A.id = B.id
AND
-- (A.name like '%tax%' and A.name like '%sale%')
(A.name like '%s')
and
(B.xtype = 'V')

AND EXISTS
(SELECT
'Union 2' as ObjectType,
AA.name AS ColumnName,
BB.name AS TableName
FROM
dbo.syscolumns AA, dbo.sysobjects BB
WHERE
AA.id = BB.id
AND
AA.name like '%stat%' AND BB.name like '%const%'
and
(BB.xtype = 'V')
AND BB.name = B.name)

/***********************************************/

And this version of the query only returns the table name:

SELECT DISTINCT B.name AS TableName
--select *
FROM
dbo.syscolumns A, dbo.sysobjects B
WHERE
A.id = B.id
AND
-- (A.name like '%tax%' and A.name like '%sale%')
(A.name like '%s')
and
(B.xtype = 'V')

AND EXISTS
(SELECT
'Union 2' as ObjectType,
AA.name AS ColumnName,
BB.name AS TableName
FROM
dbo.syscolumns AA, dbo.sysobjects BB
WHERE
AA.id = BB.id
AND
AA.name like '%stat%' AND BB.name like '%const%'
and
(BB.xtype = 'V')
AND BB.name = B.name)

--
Keith

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn