Heres a doozie? - 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 2000 Forums

Transact-SQL (2000)

Heres a doozie?

Author

Topic

PowerDominion
Starting Member

4 Posts

Posted - 2004-09-03 : 12:06:49

Hi all i have a situation where i have no idea on how to select what i need. Basically i need some steering in the right direction to solve the query.
I have two tables The first table 'DATA' holds among other things four field values of:
Country_ID
State_ID
City_ID
Clinic_ID
They can have positive integer values and a 0 if they apply to 'all'
So i could have
Country_ID = 1
State_ID = 3
City_ID = 0
Clinic_ID = 0
This means that this piece of data is available to all clinics in all cities of state 3 in country 1
Now on the second 'clinic' table has among other things the same four fields but it has to have a value for each field. No 0 or 'All' values are allowed as it is specifying the clinic and its location. eg:
Country_ID = 1
State_ID = 3
City_ID = 2
Clinic_ID = 1

My problem is how do i find out which data is available to this clinic. Yes i can look for the matches right down to clinic but what aboutif the country match on one piece of data and the rest of the fields of that piece of data are 0 this wouldnt be a match but yet it is still available. Oh yeah if for instance State_ID on the data table is 0 then all the fields below it (City_ID and Clinic_ID) are also 0.

So does anyone have a clue where to start. Sorry if i confused anyone :)
Cheers in Advance
Adam.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-03 : 12:18:17

I'm confused about your database design. You may need to normalize it.

Perhaps this database design would work for you...

You could create a Country table, State table, City table, and Clinic table.

Country has a 1 to many relationship to State
State has a 1 to many relationship to City
City has a 1 to many relationship to Clinic.

This way if you wanted to find which data is available to a clinic you could run a query such as this.


SELECT 	Country.<Data Columns>,
	State.<Data Columns>,
	City.<Data Columns>,
	Clinic.<Data Columns>
FROM	Clinic 	INNER JOIN City ON (Clinic.CityID = City.CityID)
		INNER JOIN State ON (City.StateID = State.StateID)
		INNER JOIN Country ON (State.CountryID = Country.CountryID)
WHERE	Clinic.ClinicID = @ClinicID

Perhaps you could try something like this

Dustin Michaels

PowerDominion
Starting Member

4 Posts

Posted - 2004-09-03 : 12:24:18

Cheers, I suppose i was a little vague. There are also four tables that deal with the countries states cities ect.
eg
CountryID
Description

StateID
Description
CountryID

CityID
Description
StateID

The thing is what the data table does is to assign a piece of data to many different locations. So therefore it could have three entries in the data table. The data doesnt actually exist in this table. This table just holds the location details of what clinics can access it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources