CODEWITHSUNDEEP
mysqlsqlselect
Burrii
Burrii

Reputation: 3

Select on multiple tables

I have two tables that contain completely separate information:

table: tires
columns:
Tire_id
name
model
etc 
etc

table: product
columns: 
product_id
name 
model 
etc
etc

I want to run a search on both tables at the same time, looking for keywords in the name and/or model

it should return products/tires from both databases, the ID's are not unique and might exist in both tables, being seperate products. Therefore on other parts of the site I have used a leading T or P to keep them seperate within the coding of the site.

I'm struggling to get the search to work on both at the same time.

I was thinking something like:

SELECT * FROM product OR tire WHERE name = 'keyword' OR model = 'keyword'

Upvotes: 0

Views: 129

Answers (2)

K6t
K6t

Reputation: 1845

tyr this dude

SELECT p.*,t.fieldname FROM product as p,tire as t WHERE t.name = 'keyword' OR t.model = 'keyword' or p.name = 'keyword' OR p.model = 'keyword'

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

you need a union:

select 'product' as type, product_id as id, name, model
from product
where ...
union all
select 'tire' as type, tire_id as id, name, model
from tire
where ...

Upvotes: 1

Related Questions