1

I have two tables T1 and T2.

T1 have ID,F1,F2,F3,F4,F5,F6,F7,F8

T2 have ID,T1_ID,F1,F2,F3,F4,F5,F6,F7,F8,SUM

Examples Data for T1 and T2 

 T1
 ID,F1,F2,F3,F4,F5,F6,F7,F8
  1, 1, 2, 3, 0, 0, 5, 0, 0
  2, 0, 0, 0, 1, 0, 4, 5, 0
  3, 4, 1, 3, 2, 0, 0, 0, 5
  4, 1 ,3, 4, 0, 0 ,0, 0, 0
  5, 7, 2, 1, 3, 0, 0, 0, 0
  .
  . 
  .
 T2 
 ID,T1_ID,F1,F2,F3,F4,F5,F6,F7,F8,SUM
  1,    1, 2, 3, 5, 0, 0, 3, 0, 0,100
  2,    5, 9, 8, 8, 1, 0, 0, 0, 0,200
  3,    2, 0, 0, 0, 5, 0, 6, 6, 0,300 
  4,    1 ,3, 4, 2, 0 ,0, 3, 0, 0,255 
  5,    4, 8, 8, 8, 0, 0, 0, 0, 0,155
  .
  .

Select * from T2 where T1.F1....T1.F8 have (1 and 2 and 3)

query must return records 1,2,4

  1,    1, 2, 3, 5, 0, 0, 3, 0, 0,100
  2,    5, 9, 8, 8, 1, 0, 0, 0, 0,200
  4,    1 ,3, 4, 2, 0 ,0, 3, 0, 0,255

I create this query 

Select T2.ID,T2.F1,T2.F2,T2.F3,T2.F4.T2.F5,T2.F6,T2.F7,T2.F8,T2.SUM,T1.ID
from T2 
 join T1 on T1.ID = T2.T1_ID
    where 
    (CASE WHEN ( T1_ID.F1 = 1 ) THEN T2.F1 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F1 = 2 ) THEN T2.F1 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  or
    (CASE WHEN ( T1_ID.F1 = 1 ) THEN T2.F1 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F2 = 2 ) THEN T2.F2 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  or
    (CASE WHEN ( T1_ID.F1 = 1 ) THEN T2.F1 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F3 = 2 ) THEN T2.F3 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  or
    (CASE WHEN ( T1_ID.F1 = 1 ) THEN T2.F1 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F4 = 2 ) THEN T2.F4 between 0 and 1000 end)
 and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
   .
   .
   .
  or
   (CASE WHEN ( T1_ID.F2 = 1 ) THEN T2.F2 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F1 = 2 ) THEN T2.F1 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  or
   (CASE WHEN ( T1_ID.F2 = 1 ) THEN T2.F2 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F2 = 2 ) THEN T2.F2 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  or
   (CASE WHEN ( T1_ID.F2 = 1 ) THEN T2.F2 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F3 = 2 ) THEN T2.F3 between 0 and 1000 end)
and(CASE WHEN ( T1_ID.F1 = 3 ) THEN T2.F1 between 0 and 1000 end)
  .
  .
  .

this is too big statement. How can I optimize statement ?

user2531567
  • 23
  • 3
  • You already have a similar question. (Even the same) There are two answers there, and you have no reaction to them. Please edit your previous question. – Val Marinov May 29 '17 at 12:08
  • 1
    Possible duplicate of [Best SQL statement for this table?](https://stackoverflow.com/questions/44179634/best-sql-statement-for-this-table) – Val Marinov May 29 '17 at 12:09
  • The questions are similar yes, but not equal. In second one I compare two tables and looking for better statement for this. – user2531567 May 29 '17 at 12:28
  • I see. However, it is very close. Besides, it would be good to show a little respect for the people who have separated from their time to help you. – Val Marinov May 29 '17 at 12:32
  • 1
    By the way, if the query gets too complex, it is a sign that something is wrong with the construction of the tables. – Val Marinov May 29 '17 at 12:47
  • 1
    You should change your structure to something like id, label, value instead, where `label` is then constrained to `f1`...`f8`; it will make your life in most situations a lot easier. – Mark Rotteveel May 29 '17 at 15:38
  • Thanks for a help to everybody. This is the code that I looking for . – user2531567 May 31 '17 at 08:33

1 Answers1

0
SELECT * FROM  T2
WHERE  EXISTS (   SELECT   N.ID
              FROM(   SELECT T1.ID , T1.F1 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F2 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F3 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F4 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F5 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F6 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F7 AS F
                           FROM   T1
                           UNION ALL
                           SELECT T1.ID , T1.F8 AS F
                           FROM   T1
                           UNION ALL
                       ) N
              WHERE    N.F IN ( 1, 2, 3 )
                       AND N.ID = T2.T1_ID
              GROUP BY N.ID
              HAVING   COUNT(DISTINCT N.F) = 3
          );
user2531567
  • 23
  • 3