complex query - 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)

complex query

Author

Topic

mukesh39
Starting Member

1 Post

Posted - 2010-05-25 : 09:41:16

Hi,

I am facing problem to make query to get different top 4 contracor of difefrent under construction cities who has project status as null(uncomplete) . For example: In this case, there are 2 cities with under construction, so 4 constructors should be 2 for cityID 1007 and 2 for city 1008.

tblCity
------------------
CityIDID
CityName
Status

tblContractor
----------------------

ContractorID
ContractName
CityID

tblCityProjects
------------------------
ID
Broker
projectStatus
ContractorID

Data:
------------
tblCity
==================
CityID | CityName | Status
999 CW ConstructionComplete
1000 CX ConstructionComplete
1007 CY UnderConstruction
1008 CZ UnderConstruction

tblContractor
======================
ContractorID | ContractName | CityID
1 Ca 999
2 Cb 999
3 Cc 1000
4 Cd 1000
5 Ce 1007
6 Cf 1007
7 Cg 1007
8 Ch 999
9 Ci 1008
10 Cj 1008
11 Ck 1008
12 Cl 1000
13 Cm 1007
14 Cn 1008
15 Co 1007

tblCityProjects
=====================
ID | Broker | projectStatus |ContractorID
1 Ba Done 1
2 Bb Done 1
3 Bc Done 1
4 Bd Done 1

5 Ba Done 2
6 Bb Done 2
7 Bc Done 2
8 Bd Done 2

9 Ba Null 3
10 Bb Null 3
11 Bc Null 3
12 Bd Null 3

13 Ba Null 4
14 Bb Null 4
15 Bc Null 4
16 Bd Null 4

17 Ba Null 5
18 Bb Null 5
19 Bc Null 5
20 Bd Null 5

21 Ba Null 6
22 Bb Null 6
23 Bc Null 6
24 Bd Null 6

25 Ba Null 7
26 Bb Null 7
27 Bc Null 7
28 Bd Null 7

29 Ba Null 9
30 Bb Null 9
31 Bc Null 9
32 Bd Null 9

I am facing problem to make query to get different top 4 contracor of difefrent under construction cities who has project status as null(uncomplete) . For example: In this case, there are 2 cities with under construction, so 4 constructors should be 2 for cityID 1007 and 2 for city 1008.

Please help me how can I do it?

Thank you

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-05-25 : 13:27:40

Can you explain how tblCityProjects links?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources