Creating a linked list of results?

[RobDog888]

I have a table with metadata in it that I need to present two separate but related lists for.

Table1
-----
id  |  value  
1   |  Employee_Edited  
2   |  Employee_Created  
3   |  Employee_Deleted
4   |  Manager_Hired
5   |  Manager_Fired

Now I have used sql functions to parse "Employee_Edited" into "Employee", "Edited":

sql query results
-----------
id  |  value  |  valueOnly  |  actionOnly
1   |  Employee_Edited  |  Employee  |  Edited
2   |  Employee_Created  |  Employee  |  Created
3   |  Employee_Deleted  |  Employee  |  Deleted
4   |  Manager_Hired  | Manager  |  Hired
5   |  Manager_Fired  |  Manager  |  Fired

Now I figured I could add a Distinct query and have just the "Employee" in a list but at some point I need to display the list on the webpage with each associated action. Note not all values will have the same actions.


Results 1
-----------
Employee
Manager

Then when the user selects Manager the second list will propagate to display:
Results 2
----------
Hired
Fired


I don't want to have a second db call as that would be the obvious way to solve this but not the best for performance imo.
Whats the best way to write the query for this? Using SQL and JavaScript on an MVC website

Thanks

[IkkeEnGij]

Table1
------
value    | action
-------------------
Employee | Edited
Employee | Created
Employee | Deleted
Manager  | Hired
Manager  | Fired

PK=value & action

[RobDog888]

Thanks but that's the problem. I need 2 queries out of it to produce non repeating information. Employee will have 3 pk's (id's) but I want in one list just Employee so when selected I can filter by Employee I guess as cant filter by the id since it may be one of 3 to get its associated action

[ChrisE]

Hi,

dont now if it's this your after

I tested this with Access with the Northwind DB

SELECT DISTINCT Customers.CompanyName, Customers.City, Customers.Country
FROM Customers
WHERE (((InParam([ContactTitle],[Type Owner,Sales Agent,Sales Manager]))=True));

regards
Chris

[DEXWERX]

not sure I understand the question? you say you've already got the sql?

That one query result you've shown is enough to display:

Employee
+-- Edited
+-- Created
+-- Deleted
Manager
+-- Hired
+-- Fired