This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / SQL中inner join的问题有2个表， code(code varchar(20))
group(code varchar(20),group varchar(10))
假设group表中的group的value是'grp1','grp2','grp3','grp4'
如果对于一个给定的code, 要求返回的结果中至少包括'grp1','grp2'， 该怎么写where条件
select g.*
from code c(nolock)
inner join group g(nolock) on c.code=g.code
where code='test'
and ???
-tonyhao(tonyhao);
2010-4-19
{356}
(#6020150@0)
-
select g.*
from code c(nolock)
inner join group g(nolock) on c.code=g.code and (g.group='g1' or g.group='g2')
where code='test'
-c1xwy(洪兴罩俺去战斗);
2010-4-19
(#6020343@0)
-
你的sql返回的结果可能只有‘grp1’或‘grp2’. 应该是‘grp1’和‘grp2’。 是BOTH的关系
-tonyhao(tonyhao);
2010-4-20
(#6020951@0)
-
The first of all, the ‘JOIN’ is not ‘MUST BE’ in your query (the table group contains all data needed.). For example, the following two query returns same result:SELECT * FROM group WHERE code =’test’
And
SELECT g.* FROM group g JOIN code c on c.code = g.code WHERE c.code =’test’
Secondly, suppose data in group are like following
code group
test grp1
test grp3
test grp4
test1 grp1
test1 grp2
test1 grp3
test1 grp4
…
How can you expect the query returns both grp1 and grp2 for code test?
-deep_blue(BLUE);
2010-4-20
{391}
(#6020971@0)
-
I forget to tell more. Actually, a function will check the returned record sets. If not contains at least grp1 and grp2, then false. otherwise, true.
-tonyhao(tonyhao);
2010-4-20
(#6022133@0)
-
select g.* from code c(nolock)
inner join group g(nolock) on c.code=g.code and g.group='g1'
inner join group g2(nolock) on c.code=g2.code and g2.group='g2'
where code='test'
-tonyhao(tonyhao);
2010-4-20
(#6022155@0)
-
Your query will always return nothing no matter both grp1 and grp2 in or not.My solution is
SELECT COUNT(*) FROM (SELECT DISTINCT group FROM group WHERE code ='test' AND group IN ('grp1','grp2')) A
If COUNT value equals 2 return true. Otherwise return false.
-deep_blue(BLUE);
2010-4-21
{196}
(#6023631@0)
-
should be: select c.* from code c(nolock) inner join group g(nolock) on c.code=g.code and g.group='g1' inner join group g2(nolock) on c.code=g2.code and g2.group='g2' where code='test'
-tonyhao(tonyhao);
2010-4-22
(#6026245@0)
-
There is no difference between this query and previous query.The key point is that it’s impossible to find one field to be two values in one record.
Of course, you can use outer join for your purpose. But it’s more complicated than my first solution.
-deep_blue(BLUE);
2010-4-22
{195}
(#6026739@0)
-
Returns different value. The above example, will return "A". Makes more sense because LZ wants to find out which code maps to both "grp1" and "grp2".
-wincity(toad);
2010-4-23
(#6028476@0)
-
U R right, and it works.
-tonyhao(tonyhao);
2010-4-23
(#6030680@0)
-
It's just a self-join.
-wincity(toad);
2010-4-23
(#6030717@0)
-
It does return non-empty result if the group table has one-to-many relationship between "code" and "group". For example, 2 rows (code, group): "A", "g1"; "A", "g2". The query will return "g1".
-wincity(toad);
2010-4-23
(#6028473@0)
-
is this standard sql or .net sql?
-llun(随心);
2010-4-23
(#6028399@0)
-
It's a dirty read (or called Uncommitted Read/read through locks) query of normal T-SQL.
-deep_blue(BLUE);
2010-4-23
(#6028440@0)