表一 PtoC 是产品名对应公司名,内容为
productsID ComID
1 2
2 6
6 9
...
表二 Com是公司的详细信息,内容为(ComID对应上一个表中的ComID)
ComID ComName Location .....
2 SH
6 BJ
9 SH
...怎样用productsID(比如 id=2)查询表二的内容,求一条Sql语句实现
3ks
productsID ComID
1 2
2 6
6 9
...
表二 Com是公司的详细信息,内容为(ComID对应上一个表中的ComID)
ComID ComName Location .....
2 SH
6 BJ
9 SH
...怎样用productsID(比如 id=2)查询表二的内容,求一条Sql语句实现
3ks
from 表一 A
inner join 表二 B on A.comID=B.comID
select B.*
from 表一 A
inner join 表二 B on A.comID=B.comID
where A.productsID=2
from 表一 A inner join 表二 B on A.comID=B.comID
where A.productsID=2 介意采用内连接
select com.* from com a
join PtoC b
on a.ComId=b.ComId
where productsID='2'
二,直接连接,然后按照产品条件进行过滤
select * from com where comid in (select comid from ptoc where productsid=@pid)select a.* from com a,ptoc b where a.comid=b.comid and b.productsid=@pid
select B.*
from A inner join B on A.comID=B.comID
where A.productsID=2
if object_id('products') is not null
drop table products
create table products
(
productsID int ,
ComID int
)if object_id('info') is not null
drop table info
create table info
(
ComID int ,
ComName varchar(10)
)insert into products
select 1,2 union all
select 2,4insert into info
select 2,'bh' union all
select 4,'cc'select * from products
select * from info
--方法一
select p.productsID,p.ComID,i.ComName from
products p,info i
where p.comid = i.comid
and p.productsID = 1
--方法二
select * from info where Comid = (select comid from products where productsID = 1)
/**
productsID ComID ComName
----------- ----------- ----------
1 2 bh(所影响的行数为 1 行)**/