Hello. There is a working request:

SELECT * FROM `realty_kvartiru` WHERE (dopolnitelnaya_infa LIKE 'Ленина' OR ulitsa LIKE 'Ленина' OR orientir LIKE 'Ленина')

who searches for a keyword in one realty_kvartiru table in three columns, how can you modernize it, so that it can search for a fourth agenstvo_title field from the second table phones ?

The tables have the following structure:

realty_kvartiru : id , user_id , tip , tsena , rayon_id , ulitsa , orientir , etaj , e‌​tajnost , kolichestvo‌​_komnat , nomer_doma ‌, nomer_kvartiru , sos‌​toyanie_tip , shema_t‌​ip material_tip commentary , own_typ‌​e , is_exclusive , lin‌​k , deleted_by , must_‌​called , wanted_to_re‌​move , notebook_comme‌​nt , agents_realty

phones : id , phone , is_posrednik , agenstvo_title

  • Lay out the structure of the realty_kvartiru and phones tables. - Legionary
  • Apparently something like select * from realty_kvartiru ... OR (id_записи_или_телефон_смотря_какая_у_вас_структура) in(select что-то from phones where agenstvo_title like 'xxx') - Mike
  • realty_kvartiru: id``user_id``tip``tsena``rayon_id``ulitsa``orientir``etaj``etajnost``kolichestvo_komnat``nomer_doma``nomer_kvartiru``sostoyanie_tip``shema_tip``balcon_tip``sanuzel_tip``istochnik_id``material_tip``julaya_plowad``obwaya_plowad``kuhnya_plowad``dopolnitelnaya_infa``fio``nuzno_prodat``is_deleted``created_at``updated_at``is_imported``in_blacklist``in_deleted_list``deleted_at``secretary_id``call_status``commentary``own_type``is_exclusive``link``deleted_by``must_called``wanted_to_remove``notebook_comment``agents_realty phones: id``phone``is_posrednik``agenstvo_title - ultimatum
  • @ultimatum better edit the question - Denis
  • @Denis exactly how to fix? - ultimatum

2 answers 2

If these 2 tables can be linked by id , then you can:

 SELECT * FROM realty_kvartiru r LEFT JOIN phones p ON r.id = p.id WHERE dopolnitelnaya_infa LIKE 'Ленина' OR ulitsa LIKE 'Ленина' OR orientir LIKE 'Ленина' OR agenstvo_title LIKE 'Ленина'
  • The request is triggered and does not generate errors, searches for "Lenin" in realty_kvartiru, but does not find "Sherlock" in phones -> SELECT * FROM realty_kvartiru r LEFT phones p ON r.id = p.id WHERE dopolnitelnaya_infa LIKE 'Sherlock' OR ulitsa LIKE 'Sherlock' OR orientir LIKE 'Sherlock' OR agenstvo_title LIKE 'Sherlock' PS-records with Sherlock in agenstvo_title are. - ultimatum
  • @ultimatum There is a question in the connection of the tables, most likely. Since left join , not all elements from the phones table can connect to realty_kvartiru . Check whether there is an id from the line with Sherlock in the realty_kvartiru table, and try for clarity to display the select query, commenting out the where clauses - Denis
  • Tell me in which column to check? Should match id (realty_kvartiru) = id (phones)? - ultimatum
  • If you have the word "Sherlock" in the table in the phrase for example "Sherlock Holmes" use the search ala LIKE '% Sherlock%' to find any matches regardless of where the word you are looking for in the line - Broouzer King
  • @ Broouzer King 92450 095 *** 5414 1 Sherlock - ultimatum

You can use the UNION operator http://www.w3schools.com/sql/sql_union.asp

  • In this format, it gives an error request and, accordingly, does not work -> SELECT * FROM realty_kvartiru WHERE (dopolnitelnaya_infa LIKE 'Lenin' OR or ulitsa LIKE 'Lenin' OR orientir LIKE 'Lenin') UNION ALL SELECT * FROM phones WHERE agenstvo_title LIKE 'Lenin ––––
  • Of course, the @ultimatum will give an error, because the number of columns in the tables is completely different. - Denis