Question

oracle的多条件多表查询：求解，查询符合条件的两张表中两个字段的记录

oracle的多条件多表查询：
A表和B表，A的b_id表引用B表的id
A表中有字段a_food，B表中有一字段b_name
我要查询出A表的a_food和B表的b_name字段中含有的'肉'字的所有记录该如何查？
模糊查询我会，就是不知道如何把两个条件结合起来，求解

Answer 1

select a.a_food,b.b_name
from a,b where a.b_id=b.id and b.b_name like '%肉%'

追问

select goods_name,storer.storer_network_name
from goods,storer
where goods.storer_id=storer.storer_id and storer.storer_network_name like '%肉%' or goods.goods_name like '%肉%'
这样查询出来结果不对。求解

追答

select goods_name,storer.storer_network_name
from goods,storer
where goods.storer_id=storer.storer_id and (storer.storer_network_name like '%肉%' or goods.goods_name like '%肉%')

把两个用or的查询用括号括起来试试

追问

符合goods_name like ‘%肉%’的结果出来了，但是有两条符合storer_network_name like'%肉%'的结果没出来，该如何弄

追答

你看看storer_network_name like'%肉%' 的那个id是不是没和goods表的id关联上？
也就是没有对应的id值相等
你是要把这样的也查出来吗？

追问

storer的id在goods表中作为外键的。有关联上。但是storer的id值即使在goods表中没有对应上相应的值，也就是goods_name没有值也要查询出来啊

追答

select goods_name,storer.storer_network_name
from goods left join storer
on goods.storer_id=storer.storer_id where (storer.storer_network_name like '%肉%' or goods.goods_name like '%肉%')

试试这样

Answer 2

select a.b_id,a.a_food,b.b_name from a inner join b on a.b_id = b.id where (a.a_food like '%肉%') and (b.b_name like '%肉%')
这样可以把量表中同时具有id的记录查出来。

Answer 3

select a.b_id,a.a_food,b.b_name from a,b where a.b_id=b.id and a.b_id,a.a_food,b.b_name not in (select c.b_id,c.a_food,d.b_name from a c,b d where c.b_id=d.id and c.a_food not like '%肉
%' and d.b_name not like '%肉%')

追问

运算符报错