locked
keyword search from multiple tables sql query RRS feed

  • Question

  • User-1962684534 posted

    HI all,

    i have two tables

    id col1 col2 col3

    1  dfg  dfg  err

    2  ret   ret  qwr

    3  ttt   pol   ert

     

    id   col4

    1    ert

    3    ret

     

    i want t1.col1,t1.col2,t1.col3,t2.col4 from table1 t1,table2 t2 where t1.id=t2.id

    and

    t1.col1 like '%r%' or t1.col2 like '%r%' or t1.col3 like '%r%' or t2.col4 like '%r%'

    finally, i want 3 columns from table1 and 1 column from table2, where ID equal and where these columns contain 'r'

    pls let me know the how can i write the query for this..

    Thanks

    Wednesday, March 16, 2011 5:15 AM

Answers

  • User-2139489267 posted

    Check out below :

    select a.col1,a.col2,a.col3,b.col2
    from table1 a inner join table2 b on b.id = a.id
    where (a.col1 like 'r%' or a.col2 like 'r%' or a.col3 like 'r%' or b.col2 like 'r%')

    I've not taken % before r and assuming that it was just an example you gave.

    You were having cross join, instead go for inner join if ids will be available in both the tables else go for left join.

    For bettter understanding of Joins, please go through this link

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 6:18 AM

All replies

  • User1113735518 posted

    select a.col1,a.col2,a.col3,b.col2 from table1 a inner join table2 b on b.id = a.id

    where a.col1 like '%r%' or a.col2 like '%r%' or a.col3 like '%r%' or b.col2 like '%r%'

    Wednesday, March 16, 2011 5:48 AM
  • User-2139489267 posted

    Check out below :

    select a.col1,a.col2,a.col3,b.col2
    from table1 a inner join table2 b on b.id = a.id
    where (a.col1 like 'r%' or a.col2 like 'r%' or a.col3 like 'r%' or b.col2 like 'r%')

    I've not taken % before r and assuming that it was just an example you gave.

    You were having cross join, instead go for inner join if ids will be available in both the tables else go for left join.

    For bettter understanding of Joins, please go through this link

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 6:18 AM