SQL where term is equals or like (two tables)

Question

With MySQL, I need to find all records where a column is like a term OR the term is equals a column but in another table.

For example:

select *
from table_a TA,
     table_b TB
where TB.number = '1447'
   or TA.subject like '%1447%'

In practice, I'm searching for a value, it can be on TB.number OR TA.subject. This SQL above is returning multiple records and it not matches the search.

Whats sis the common column between `table_a` & `table_b`? – Vikrant Nov 28 '16 at 11:58 — Vikrant, Nov 28 '16 at 11:58

score 3 · Accepted Answer · answered Nov 28 '16 at 11:58

3

You are doing a cross product of the two tables. You need to restrict the number of results using some JOIN criteria.

Read more on this here: http://www.w3schools.com/sql/sql_join.asp

answered Nov 28 '16 at 11:58

Adi

4,149
4
25
41

score 1 · Answer 2 · answered Nov 28 '16 at 11:59

1

Yeah, you need to do a JOIN among the table on a common column like

select * 
from table_a TA
join table_b TB 
on TA.id = TB.id
where TB.number = '1447' 
or TA.subject like '%1447%'

answered Nov 28 '16 at 11:59

Rahul

76,197
13
71
125

score 0 · Answer 3 · answered Nov 28 '16 at 12:01

0

select * from table_a TA, table_b TB where TB.number = '1447'  
UNION ALL 
select * from table_a TA, table_b TB where TA.subject like '%1447%'

answered Nov 28 '16 at 12:01

Mansoor

4,061
1
17
27

score 0 · Answer 4 · answered Nov 28 '16 at 12:10

0

Seems like the two table are more or less unrelated. Do a UNION ALL instead of JOIN:

select * from table_a TA where TA.subject like '%1447%'
UNION ALL 
select * from table_b TB where TB.number = '1447'

answered Nov 28 '16 at 12:10

jarlh

42,561
8
45
63

SQL where term is equals or like (two tables)

4 Answers4