Table with multiple serviced states

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

General SQL Server Forums

Database Design and Application Architecture

Table with multiple serviced states

Author

Topic

marly
Starting Member

6 Posts

Posted - 2008-03-08 : 23:50:46

I'm creating a database of truckers, which has company name, address, etc. I also want to include which states that they service.

For example:
Trucker A, services California, Nevada, and Oregon
Trucker B, services Nevada and Oregon
Trucker C, services Oregon
Trucker D, services New Mexico

When searching, if I'm looking for a trucking company that services Oregon, I run a search for Oregon, only Trucker A, B, and C will show up, but not Trucker D.

I created a table called truckers, with a primary key of Trucker_ID, put in columns, for company name, address, etc.

Next I create a table for the states, what’s the best way of creating that table?

Would I create a table with 50 different columns, putting in a State_Id column, and then relating Trucker_ID with State_ID, or is there a better way of going about that?

Thanks,

marly

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 00:18:29

You would create have a 50 row, 3 column table called StateID,StateName, StateAbbrev

Create a table (ServiceStates) with All of the TruckerID's and related stateid (1 to 50) they service. 2 columns

This would be what's called a one to many relationship

The TruckerID would reference the truckers table, the StateID would reference the Statename table.

The Primary key of trucker ID would link to the state table on the Trucker ID.

You would not add 50 columns for this...

here is a quick sample of how I would see/use it perhaps


Create Table #Truckers (TruckerID int identity(1,1),[name] char(5) null)
Insert Into #Truckers ([name])

Select 'Paul' UNION ALL
Select 'Peter' UNION ALL
Select 'Jeff' Union ALL
Select 'Tom'  

Create Table #StateNames (StateID int not null, StateAbbrev char(2) not null,StateName char(21) not null)

Insert Into #Statenames(StateID, StateAbbrev, StateName)
Select 1,'CA','California' UNION ALL
Select 2,'NV','Nevada' UNION ALL
Select 3,'OR', 'Oregon'

Create Table #ServiceStates (TruckerID int not null,StateID int not null)
Insert Into #ServiceStates (TruckerID, StateID)
Select 1,1 UNION ALL
Select 1,2 UNION ALL
Select 1,3 UNION ALL
Select 2,1 UNION ALL
Select 3,2 

Select t.*,sn.*,ss.*
FROM #Truckers t inner join #ServiceStates ss on t.TruckerID = ss.TruckerID
				 inner join #StateNames sn on ss.StateID = sn.StateID



/*results
TruckerID	name	StateID	StateAbbrev	StateName	TruckerID	StateID
1	Paul 	1	CA	California           	1	1
1	Paul 	2	NV	Nevada               	1	2
1	Paul 	3	OR	Oregon               	1	3
2	Peter	1	CA	California           	2	1
3	Jeff 	2	NV	Nevada               	3	2 */


--a SQL 2005 pivot sample
    
--
Select [Name],[NV] as Nevada,[CA] as California,[OR] as Oregon 
FROM ( Select t.TruckerID,t.Name,sn.StateAbbrev FROM #Truckers t inner join #ServiceStates ss on t.TruckerID = ss.TruckerID
				 inner join #StateNames sn on ss.StateID = sn.StateID) s
PIVOT ( COUNT (TruckerID) FOR StateAbbrev IN ([NV],[CA],[OR])) AS pvt
ORDER BY [Name]
/*
Name	Nevada	California	Oregon
Jeff 	1	0	0
Paul 	1	1	1
Peter	0	1	0
*/

Drop Table #Truckers
Drop Table #Servicestates
Drop Table #StateNames

Poor planning on your part does not constitute an emergency on my part.

marly
Starting Member

6 Posts

Posted - 2008-03-14 : 13:54:03

Thank you very much for your quick response, your response was very helpful, and I apologize for my delayed response.

One question, in a situation such as this where you have a one-to-many relationship with three tables when I run the query from your example Paul shows up three times and I only want Paul to show up once, but California, Nevada, and Oregon to display, what is considered best practice for filtering that out?

Would it be better to do it through my query in SQL or would I do it in code after I've received the query?

When you normalize I can think of plenty of other situations where this would turn up.

After reading the O'Reilly SQL cookbook, I was able to return a query back filtering out Paul, but it returns NULL, so I'm not sure that's best to do?

I'm loading the data up in a grid control on a asp.net page.

Thanks,

marly

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-14 : 15:41:12

Hi,

this topic is very similar and may help with what you want...sounds like you want to put the multiple rows into "1 column" of your output, different than both my options.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources