Select on multiple tables

Question

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'

Denis de Bernardy · Accepted Answer · 2011-06-16 11:27:04Z

1

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 ...

answered Jun 16, 2011 at 11:27

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

Comments

K6t · Accepted Answer · 2011-06-16 11:29:41Z

0

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'

answered Jun 16, 2011 at 11:29

K6t

1,8452 gold badges13 silver badges21 bronze badges

Collectives™ on Stack Overflow

Select on multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related