Regarding English Query - Request help

Question

I work as a researcher at Tata Research Development and Design Centre, India . I am exploring the available Natural Language Interfaces for database.While exploring MS English Query, I found that certain type of relationship does not give appropriate answer.

Our schema looks like as shown below:

Schema: 
Customer ( customer_id , customer_name, customer_address) 
Transaction ( transaction_id , customer_id_1, customer_id_2, amount) 

CUSTOMER 
cuctomer_id                customer_name        customer_address 
1                        John                        abc 
2                        Rohit                        pqr 
3                        Mark                        xyz 

TRANSACTION 
transaction_id        customer_id_1        customer_id_2        amount 
1                        1                        2                        200 
2                        1                        3                        300 
3                        2                        1                        300 

Here, customer_id_1 and customer_id_2 references to Customer.customer_id. More than two attributes of a table refers to same primary key attribute in another table.

Query : “Give me all transactions between John and Rohit”

The entities we made were Customer and transaction; for the given English query,following relationship was made between customers and transactions-

Relationship Type : Noun Verb 
Relationship: Transaction are 
Preposition Clause: between Customer(customer_id_1) 
Preposition Clause: and Customer_1(customer_id_2) 

Here we defined two different roles of customers(through 2 join path-customer_id_1 and customer_id_2)

The output we expect was transactions with trasaction_id as 1 and 3, but it was joining on customer_id_1 only and gave result as

Expected Output- 
transaction_id        customer_id_1        customer_id_2        amount 
1                        1                        2                        200 
3                        2                        1                        300 

Actual Output- 
transaction_id        customer_id_1        customer_id_2        amount 
1                        1                        2                        200 
2                        1                        3                        300 

This is happening because of ambiguity between default and and and we defined in preposition clause..

We also tried to create another relationship

1) 
Relationship Type : Noun Verb 
Relationship: Transaction are 
Preposition Clause: by Customer(customer_id_1) 
Preposition Clause: to Customer_1(customer_id_2) 
2) 
Relationship Type : Noun Verb 
Relationship: Transaction are 
Preposition Clause: by Customer(customer_id_2) 
Preposition Clause: to Customer_1(customer_id_1) 

The output we expect was transactions with trasaction_id as 1 and 3, here It took join on both customer_id_1 andcustomer_id_2, But only relationship 1 was used and not both 1 and 2, so output was as follows

Expected Output- 
transaction_id        customer_id_1        customer_id_2        amount 
1                        1                        2                        200 
3                        2                        1                        300 

Actual Output- 
transaction_id        customer_id_1        customer_id_2        amount 
1                        1                        2                        200 

Is there a work around for creating relationships that would make possible the above query to be understood by MSEQ? Hope someone help us by suggesting some appropriate solution.

Answer 1

I couldn't understand what you wrote in length. It would make sense if you could provide what exact query you had written. Anyway, try the following query, it will work.

SELECT [Transaction_Id]
      ,[Customer_Id_1]
      ,[Customer_Id_2]
      ,[Amount]
  FROM [TEST].[dbo].[Transaction] WHERE ((Customer_Id_1 = 1 AND Customer_Id_2 = 2)OR(Customer_Id_1 = 2 AND Customer_Id_2 = 1))