sql - 加入 sql server 给我更多行

Question

我有两张 table

1. User

    id      language            program  status
    1       English/Spanish     CF/CA       1
    2       English             C           1
    3       Spanish             CF          1
    4       Greek               CA          1

2. Entry

   language             program
    English             CF
    English             C
    Spanish             CF
    Spanish             C

我的要求是从 User 表中获取详细信息，该表在 Entry 表中有 language and program row 和 status = 1

    eg:- User ID 1 has language English/Spanish and Program CF and 
User id 2 has language English and Program C and user 3 has 
spanish and CF so only 3 rows will be retrieve

我正在使用下面的查询

select u.*
from user u inner join
Entry e
on( u.language like '%'+e.language+'%'
and u.program like '%'+e.program+'%')
where [status] = 1

我得到低于结果。我知道一些 pblm 加入我无法修复它。请帮忙

User id     language           program     Status
1           English/Spanish    CF/CA        1
1           English/Spanish    CF/CA        1
1           English/Spanish    CF/CA        1
1           English/Spanish    CF/CA        1
2           English            C            1
3           Spanish            CF           1
3           Spanish            CF           1

Answer 1

一个解决方案是添加group by u.id, u.language, u.program, u.status因为您只从用户表中选择数据。

但是你也可以考虑重新设计你的表，这样你可能会得到一个更好的解决方案来解决这个问题，例如为语言和程序创建表。

[编辑:修复错字]