I am not an expert in SQL. I am just a beginner.
我不是SQL的专家。我只是个初学者。
My Table structure:
我的表结构:
Partner table: objectid, partner[which is partner id], partnerfunc
合作伙伴表:objectid,合作伙伴[合作伙伴ID],partnerfunc
customertable: customerid, customer type
customableable:customerid,客户类型
Given is object id.
给定是对象id。
Queries
select partner from partners where object = '352846';
It returns 6 paartner informations.
它返回6个paartner信息。
1
2
3
4
5
6
select partnerfunction from partners where object = '35284624';
A
B
C
Z
X
Y
It returns corresponding partner functions.
它返回相应的伙伴功能。
I need to get customer type from customer table with the help of given data.
我需要在给定数据的帮助下从客户表中获取客户类型。
select customertype from customer where customerid in (select partner from
partners where iobject = '352846');
It returns only 4 informations.
它只返回4个信息。
Owner
Owner
Vendor
Vendor [Remaining two values may be em]
I just got confused. I want to print corresponding partner and partnerfunctions so that i can check the result.
我只是感到困惑。我想打印相应的合作伙伴和合作伙伴功能,以便我可以检查结果。
First i just changed the above query to as follows
首先,我刚刚将上述查询更改为如下
select customertype from customer, iobj_partners where customerid in
(select partner from iobj_partners where iobject = '352846') and iobject
= '352846';
It returns 24 results
它返回24个结果
owner [12 rows]
vendor [12 rows]
Should i use distinct? Why does it return 24 values? I am really confused with the query i have written. I totally misunderstood the query.
我应该使用不同的?为什么它会返回24个值?我真的很困惑我写的查询。我完全误解了这个问题。
And i tried to print it along with partner and partner functions,
我试图将它与合作伙伴和合作伙伴的功能一起打印出来,
select customertype, partner, partnerfunction
from customer, partners
where customerid in (select partner from partners where iobject =
'352846')
and iobject = '352846'
group by customertype, partner, partnerfunction;
It returns 12 rows. 6 vendors, 6 owner.
它返回12行。 6个供应商,6个所有者。
But i understood that it will return as follows
但我明白它将返回如下
partner customertype partnerfunction
1 owner A
2 B
3 owner C
4 X
5 vendor Y
6 Z
But it returns more than what i wanted? Please clear my basic doubt
但它的回报超过我想要的回报?请清除我的基本疑问
1 个解决方案
#1
Sounds like you need to use a join
, probably an outer join
to return the null customers:
听起来你需要使用一个连接,可能是一个外连接来返回null客户:
select p.partner, c.customertype, p.partnerfunction
from partner p
left join customer c on p.partner = c.customerid
where p.object = 352846
Here's a pretty good reference:
这是一个非常好的参考:
- A Visual Explanation of SQL Joins
SQL连接的可视化解释
#1
Sounds like you need to use a join
, probably an outer join
to return the null customers:
听起来你需要使用一个连接,可能是一个外连接来返回null客户:
select p.partner, c.customertype, p.partnerfunction
from partner p
left join customer c on p.partner = c.customerid
where p.object = 352846
Here's a pretty good reference:
这是一个非常好的参考:
- A Visual Explanation of SQL Joins
SQL连接的可视化解释