Using a lookup table

Question

Using a lookup table

Steve Wilson-461924

Old Hand

Points: 385
More actions
July 9, 2007 at 3:37 am

#173436

Hi Guys,
First post here. Can you help me please?
For each row of element data I have in a table there can be 1 or more responsible persons for that element.
In each datarow I need to show the element and all the responsible people in the adjacent cell. IE.
+---------------------------------------+-----------------------------+
|                   Element                   |        Responsible            |
+---------------------------------------+-----------------------------+
| Development and execution          |     Jason Davis               |
|                                                   |     Steve Wilson             |
|                                                   |     Jake Smith                |
+----------------------------------------+----------------------------+
| Logistics                                       |     Andrew Fuller           |
+----------------------------------------+----------------------------+
How can I do that. I can pull out the responsible people if there is only 1 but I do not know how to put 2 or more in the same cell.
I hope that makes sense. Thanks for your help I really appreciate it.
Best Regards,

Steve.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

You cannot store two rows in same cell. Bad workaround would be to use comma or other seperator to seperate them. But this is BAD design of RDBMS. Moreover you will have trouble updating one record. If you are still in design phase of project, do below.

You can create one table called Element_Master which will have ID and Element Description.

1. Create table ElementMaster (ID int, Desc varchar(100))

Insert into ElementMaster (1, 'Development and execution')

Insert into ElementMaster (2, 'Logistics')

Then you can use Mapping table

2. Create table Element_Responsible_Map (ID int, Resposible_Person varchar(80))

this table will hold rows like

1, 'Jason Davis'

1, 'Steve Wilson'

1, 'Jake Smith'

2, 'Andrew Fuller'

In ideal world, i would replace name with their employee ids and refer them to employee master.

Makes sense?

Queries, updates, deletes would be very simple and your life would be easier.

Steve Wilson-461924 Old Hand Points: 385 More actions · Answer 2

Hi,

Thanks for the reply.

I Understand what you are saying but what would the extacted data look like.

Would it show:

Development and execution Jason Davis

Development and execution Steve Wilson

Development and execution Jake Smith

Which is a bit rubbish.

I would like it to show:

Development and execution Jason Davis

Steve Wilson

Jake Smith

What sql select code would I use to pull the data out.

Thanks aagin for your help & time.

Best Regards,

Steve.

bledu SSChampion Points: 12335 More actions · Answer 3

how the data looks is really the business of your reporting tool. Which tool are you using for reporting?

In most case in your reporting tool you can use the grouping functionality.

And please do consider the advise you have been given above.

Everything you can imagine is real.

Steve Wilson-461924 Old Hand Points: 385 More actions · Answer 4

Hi There,

Thanks for the reply.

I am using Visual Studio 2005 and this info is being displayed in a grid. I will use the advice given but am wondering how I go about displaying properly.

Best Regards,

Steve.

bledu SSChampion Points: 12335 More actions · Answer 5

you can not use the data grid view in that manner, the nearest you can get for what you want is using the listview with grouping. else you will have to buy the add ins which have that functionality like infragistics

but i would advise you to post your requirement on a .net user group

Everything you can imagine is real.