RowSet if TestID exists across all IDs

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

RowSet if TestID exists across all IDs

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-03-31 : 05:12:59

How can I write a query that may return row set only if TestID exists across all IDs, Like in the row set below all rows for 1 should not be displayed as against 85 it is NULL
ID TestID TestName
85 NULL NULL
41 1 NULL
42 1 NULL
83 1 NULL
85 2 NULL
41 2 NULL
42 2 NULL
83 2 NULL
85 3 NULL
41 3 NULL
42 3 NULL
83 3 NULL

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-31 : 06:17:58

This is probably not the best way but

/*
create table moo (
[ID] int ,
TestID int ,
TestName int
)

INSERT INTO moo values (85, NULL, NULL)
INSERT INTO moo values (41, 1, NULL)
INSERT INTO moo values (42, 1, NULL)
INSERT INTO moo values (83, 1, NULL)
INSERT INTO moo values (85, 2, NULL)
INSERT INTO moo values (41, 2, NULL)
INSERT INTO moo values (42, 2, NULL)
INSERT INTO moo values (83, 2, NULL)
INSERT INTO moo values (85, 3, NULL)
INSERT INTO moo values (41, 3, NULL)
INSERT INTO moo values (42, 3, NULL)
INSERT INTO moo values (83, 3, NULL)
*/

SELECT moo.[ID], moo.testid FROM moo
INNER JOIN
(SELECT [testID], count([id]) as occurs FROM moo GROUP BY [testid]) ocs
ON ocs.testid = moo.testid
INNER JOIN
(SELECT max (occurs) as [amount] from (SELECT [testID], count([id]) as occurs FROM moo GROUP BY [testid]) a) b
ON b.amount = ocs.occurs

-------
Moo. :)

ijprasad
Starting Member

29 Posts

Posted - 2005-03-31 : 08:01:27

try is
select A.id, A.testid From Table A, Table B
Where A.TestId = B.Id

Inderjeet

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 09:30:01

As always, I hope your table has a primary key. You can't do much useful with SQL if your tables do not have one. From your sample data, there does not appear to be a PK so most set-based SQL statments cannot be expected to provide consistent or accurate results.

Inderjeet -- use proper JOIN syntax. Separating the join conditions from other criteria will make your query much more readable and less likely to produce accidentally cross joins. Also, it is a good habit to get into when you need to start using LEFT OUTER JOINS.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources