2

I have a query:

Select A.col1 as col1, B.col5 as col2, C.col10 as col3
    FROM Table A 
    JOIN Table B on(A.col2 = B.col2)
    JOIN table C on(C.col1 = B.col3)
UNION
SELECT A.col1 as col1, B.col5 as col2, NULL as col3
     FROM Table A 
     JOIN Table B on (A.col2 = B. col2) 
           where A.col4 != 'somevalue'

Any way of making this faster??

Table A
--------
col1         col2          col4
gerry          1           'somevalue'
harry          2           'othervalue'
 tom           3           
sarah          4           'somevalue'

col2 of table A is the primary key

Table B
-------
col2         col3    col5
 1            45      34
 1            34      23
 1            56      67
 2            34      56
 Primary key of B is (col2, col3)
Table C
-------
col1     col10
 34       'heyya'
 467      'tyga'
 56       'pity'

Primary key of C is also composite. one of these keys is col1

 Output:
 col1     col2    col3
 gerry    23       'heyya'
 gerry    67       'pity'
 gerry    34        NULL
 harry     56      'heyya'

So values of B that have presence in C or have 'somevalue' in A are called. Also values having both are also called.

Paroma Sengupta
  • 31
  • 3
  • why do you want to have some row twice (only col3 is different) ? – Bernd Buffen Jan 11 '17 at 21:19
  • I agree that some row comes twice. I cant figure out a of eliminating it. the query was first made by union-ing two separate queries that already existed and made sense but now I am seeing this and am trying to optimize and can't find a way – Paroma Sengupta Jan 11 '17 at 21:31
  • provide raw data and expacted result please – Alex Jan 11 '17 at 21:35
  • The output provided is your expected result or what? yuor query does not bring this output. Please clarify your goal. Here is fiddle http://sqlfiddle.com/#!9/cf5d7/1 Next time you should better prepare it by yourself. – Alex Jan 12 '17 at 14:47

1 Answers1

1

is this OK for you ? so you get all rows and if A.col4 <> 'somevalue' then you get NULL for col3

Select
      A.col1 as col1
    , B.col5 as col2
    , C.col10 as col3
    , if(A.col4 != 'somevalue', 1, 0) as col4
FROM Table A 
JOIN Table B on(A.col2 = B.col2)
    JOIN table C on(C.col1 = A.col3);
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39