Selecting data in the table with KeyValuePair - SQL

Question

I'm trying to select the data from key value pair the problem is every time I'm using AND it only displaying a one record which is equal to Value column but if I tried to use OR it giving me wrong return of record. I already did some research but unfortunately I'm not even close to find a solution.

Here's what I'm trying:

    SELECT 
    u.userId
    ,u.[Key]
    ,u.[Value]
    ,u.ReportType
    FROM
    OrderItemu 
    WHERE u.userId = 1 AND u.ReportType = 1 AND u.[Value] = '18000981'

MyTable:

 OrderId     UserId     RowId    Key             Value      ReportType
   1         1           1       OrderNumber     18000981   1
   2         1           1       Item            ToyCar     1
   3         1           1       Price           1000       1
   4         1           2       OrderNumber     18000401   1
   5         1           2       Item            Camera     1
   6         1           2       Price           570        1

Result when I'm using AND clause:

   OrderId   UserId     RowId    Key             Value      ReportType
   1         1           1       OrderNumber     18000981   1

Result when I'm using OR clause:

 OrderId     UserId     RowId    Key             Value      ReportType
   1         1           1       OrderNumber     18000981   1
   2         1           1       Item            ToyCar     1
   3         1           1       Price           1000       1
   4         1           2       OrderNumber     18000401   1

The result that I want to achieve:

 OrderId     UserId     RowId    Key             Value      ReportType
   1         1           1       OrderNumber     18000981   1
   2         1           1       Item            ToyCar     1
   3         1           1       Price           1000       1

Have you tried some parentheses? It seems that you're not very familiar with boolean logic. — tworogue, Mar 22 '19 at 07:54
All six rows has userId = 1 and reportType = 1 so your OR version should have included all 6 rows. It is not clear to me what you want to achieve with u.[Value] = '18000981' comparison, it looks like you should ´have `AND rowId = 1` instead — Joakim Danielson, Mar 22 '19 at 07:56
You may want to use a subquery to get 'rowId' for which you have such value '18000981', then just get all rows with that 'rowId' from the table. — tworogue, Mar 22 '19 at 07:59
yes I'm not very familiar with that but I already tried put some parentheses but still not getting the result that I want — Shiba Tatsuya, Mar 22 '19 at 08:16
that data is uploaded in excel file so the rowId is not possible because if I have a large data (may cause many rows) it will going to have a different rowId. what I want to achieve get a data by using userId and reporttype with the value of OrderNumber in KeyValuePair — Shiba Tatsuya, Mar 22 '19 at 08:20

score 1 · Answer 1 · answered Mar 22 '19 at 08:03

1

You can try to use exists subquery.

SELECT *
FROM OrderItemu oi
WHERE exists
(
    SELECT 
        1
    FROM
        OrderItemu u
    WHERE u.userId = 1 AND u.ReportType = 1 AND u.[Value] = '18000981' and u.RowId = oi.RowId
)

answered Mar 22 '19 at 08:03

D-Shih

44,943
6
31
51

score 0 · Answer 2 · answered Mar 22 '19 at 07:59

0

SELECT u.userId ,u.[Key] ,u.[Value] ,u.ReportType FROM
OrderItem u 
WHERE u.UserId = 1 AND u.RowId = 1;

this query will show what you posted that you want returned( meaning it will show all users with ID = 1 and all RowId = 1 aswell )

answered Mar 22 '19 at 07:59

Andrei Fiordean

223
3
14

score 0 · Answer 3 · answered Mar 22 '19 at 08:39

You need to use parentheses and both the key and value column

SELECT 
u.userId
,u.[Key]
,u.[Value]
,u.ReportType
FROM
OrderItemu 
WHERE u.userId = 1 
  AND u.ReportType = 1 
  AND (u.[key] = 'OrderNumber' AND u.[Value] = '18000981' OR (u.[key] <> 'OrderNumber'))

Selecting data in the table with KeyValuePair - SQL

3 Answers3