Sql query help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Sql query help

Author

Topic

callorchat
Starting Member

1 Post

Posted - 2007-12-10 : 12:01:42

I am having few problems for constructing a query for the following scenario.

I have two tables called X and Y (both are related and table Y contains the foreign key). Table Y has got 2 more columns called type and status and Table X has got one more column called priority.

1) Y.type can contain values 1 or 2 (that means table Y can hold max two records for every record in Table X)

2) X.priority can contain values 1, 2 or 3 (if the priority value is 1 or 2 then table Y will have two related records, and if the priority value is 3 then table Y will have only one related record)

3) Y.status can contain values 1, 2, 3

So the query i want to write is (for example when Y.status is 1)

return all records from table X when

if X.priority = 1 or 2 then the both the related records in the Table Y having the status 1 (ie. if one of the record has got a differnt status (for example 2) then the record in the Table X must be excluded from the search)

if X.priority = 3 then return the record if the Y.status =1 (as for prority 3 Table Y will have only one related record).

Please let me know if you want any further clarification.

Any help is greatly appreciated

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-10 : 12:25:01

SELECT X.{keycol},X.Priority
FROM X t1
INNER JOIN (SELECT {keycol},COUNT(*) AS 'YCount'
FROM Y
WHERE status=1
GROUP BY {keycol}) t2
ON t1.{keycol}=t2.{keycol}
WHERE ((t1.priority=1 OR t1.priority=2) AND t2.YCount=2)
OR (t1.priority=3 AND t2.YCount=1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources