Searching for different columns in different mysql tables

Question

I execute this two queries one by one but i need to execute this two queries as one query same time and get result from mysql database once

I tried UNION SELECT but because of select different coulmns (model,modelsnumber) from different tables it's not working

Does anybody knows sql query structure to help me to solve this problem?

query 1

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM realestate 
    USE INDEX(idx_post_id,idx_name,idx_price,idx_username,idx_poster_folder_id)
    WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

query 2

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM cars 
USE INDEX(idx_post_id,idx_name,idx_price,idx_username,idx_poster_folder_id)
WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

I tried

$query = "SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM realestate 
WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
UNION
SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id FROM cars 
WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10";

@Akina UNION SELECT not working at all i need different sql query structure — Tedo Dhdhdjdodk
– Tedo Dhdhdjdodk, Commented Mar 23, 2021 at 9:36
What are you talking about, “i need different sql query structure”? The column list is identical in both queries (and I guess we can assume they are of the same types as well?), and that is the criterion for whether or not you can combine two result sets via UNION. — C3roe
– C3roe, Commented Mar 23, 2021 at 9:41
but because of select different coulmns (model,modelsnumber) from different tables ??? Output lists in your separate queries are absolutely the same. — Akina
– Akina, Commented Mar 23, 2021 at 9:41
“but because of select different coulmns (model,modelsnumber) from different tables it's not working” - you are not selecting those columns, you are using them in your WHERE clause. That’s two totally different things to begin with. — C3roe
– C3roe, Commented Mar 23, 2021 at 9:42

Akina · Accepted Answer · 2021-03-23 09:38:55Z

1

Separate subqueries with ORDER BY and/or LIMIT in UNION must be enclosed with parenthesis:

$query = "
   (SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id 
    FROM realestate 
    WHERE post_name LIKE ? OR description LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
   )
UNION
   (SELECT post_id,SUBSTR(post_name, 1 ,30) as post_name,price,username,poster_folder_id 
    FROM cars 
    WHERE post_name LIKE ? OR description LIKE ? OR model LIKE ? OR modelsnumber LIKE ? OR price LIKE ? ORDER BY post_id DESC LIMIT 10
   )";

answered Mar 23, 2021 at 9:38

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Searching for different columns in different mysql tables

query 1

query 2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

query 1

query 2

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related