Populate SQL table column from another table

Question

I have a jqGrid(latest free jqGrid version) table filled with data from the database(MS SQL) using a Java REST service. One of the jqGrid columns its a drop down list with 6 options.

My asignment is to create another database table that contains the Drop Down List values and using foreign keys / primary keys i have to automaticly populate the first table DDL values.

I am failing to understand the logic behind it. Can someone expain this to me? How can i achieve this. Do i only send a ID from jqGrid and depending on that ID(1,2,..,6) it chooses what to set in table#1 DDL column(comparing the id sent with the ID's of the table that contains the DDL Values)?

I am getting the feeling i am not expressing myself well... Hope you guys understand me.

score 2 · Accepted Answer · edited May 23 '17 at 12:31

We can start with the database table. It could look like

CREATE TABLE dbo.OrderStatus (
    Id int IDENTITY NOT NULL,
    Name nvarchar(100) NOT NULL,
    CONSTRAINT PK_LT_OrderStatus PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UC_LT_OrderStatus_Name UNIQUE NONCLUSTERED (Name)
)

It allows to address any Item of such OrderStatus table by Id or by Name. The UNIQUE CONSTRAINT don't permit to add name duplicates. Another table Order can have column

CREATE TABLE dbo.Order (
    Id int IDENTITY NOT NULL,
    OrderStatusId int NOT NULL,
    ...
)

ALTER TABLE dbo.Order  WITH CHECK ADD  CONSTRAINT FK_Order_OrderStatus
FOREIGN KEY(OrderStatusId) REFERENCES dbo.OrderStatus (Id)

During filling the grid with the data you have two main options: using OrderStatusId in the data or the usage of the corresponding Name from dbo.OrderStatus:

SELECT Id,OrderStatusId, ...  FROM dbo.Order

or

SELECT Id,os.Name AS OrderStatus, ...
FROM dbo.Order AS o
    INNER JOIN dbo.OrderStatus AS os ON os.Id=o.OrderStatusId

If you decide to fill the grid with ids (OrderStatusId values) then you will have to use formatter: "select" to display the text in the corresponding column (see here). It required that you would have to have editoptions.value filled with all different values from dbo.OrderStatus. The best way to implement this would be to extend the response from the server for filling the grid with your custom data and to use beforeProcessing to set editoptions.value. I described the scenario in the answer. I'll remind you it below.

Let us the response from the server looks like

{
    "rows": [{...}, {...}]
}

If the returned data looks just like

[{...}, {...}]

then you should include the wrapping. I suggest that you made

SELECT Id,Name FROM dbo.OrderStatus

additionally to making the main select from dbo.Order (SELECT * FROM dbo.Order) and you place both results in the server response:

{
    "orderStatus": [{"id":1, "name":"Pending"}, ...],
    "rows": [{...}, {...}]
}

To process orderStatus you need to add the following beforeProcessing, which read orderStatus and set editoptions.value of the orderStatus column of the grid:

beforeProcessing: function (response) {
    var $self = $(this), orderStatus = response.orderStatus, i, values = "";
    if (orderStatus != null && orderStatus.length > 0) {
        for (i = 0; i < orderStatus.length; i++) {
            if (values.length > 0) {
                values += ";";
            }
            values += orderStatus[i].id + ":" + orderStatus[i].name;
        }
        $self.jqGrid("setColProp", "orderStatus", {
            editoptions {
                value: values
            }
        });
        if (this.ftoolbar) { // filter toolbar exist
            $self.jqGrid("destroyFilterToolbar");
            $self.jqGrid("filterToolbar");
        }
    }
}

The above code is not tested, but I hope that the main idea should be clear from it.

Hello Oleg and thank you for the reply. Just to make sure i understand... I have 2 tables. The main one and the second holding the drop down list values. When i make the request to the database from my Java REST service, i create a inner join taking the values from the main table and the values from the DDL value holding column from the second table and i feed them to the Grid. Correct? Also, when it comes to inserting data to this tables from jqGrid, do i create 2 insert statements? One that sends the data to the main table and 1 to send the DDL value to the second table? — IvanSt, Jan 18 '16 at 09:42
@IvanSt: I suggested you **two options**: with or without the usage of `formatter: "select"`, but with the usage of `edittype: "select"` in both cases. The second table `dbo.Order` contains the references to ids `OrderStatusId` instead of texts (like 1 instead of `'Pending'`). If you makes INNER JOIN, then you *don't need* to use ids on the client side. The example which I described more detailed is the usage of SELECT without INNER JOIN. One fills the grid with id of `dbo.OrderStatus`. One can modify the above code and fill `value: "Pending:Pending;Active:Active"` from `orderStatus`. — Oleg, Jan 18 '16 at 09:55
@IvanSt: You will typically have only **one** insert in `dbo.Order`. The user will see `orderStatus` column as drop-down and he will be able to choose existing status value from predefined list of values. One don't need to allow the user to create new status. Thus one will insert only once in `dbo.Order`. If you use SELECT with INNER JOIN, then the user send back *the text of orderStatus*. So you will need to select Id by Name from `dbo.OrderStatus` to have `OrderStatusId` for inserting/editing of the items of `dbo.Order`. — Oleg, Jan 18 '16 at 10:00

Populate SQL table column from another table

1 Answers1