Clever Geek Handbook

Join sql server give me more lines

I have two tables

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

My requirement is to get details from a table User

that has language and program row in Entry table

both 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

I am using below query

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

I am getting below result. I know some pblm are joining, I cannot fix it. Pls help

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
+3


source to share


2 answers 


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


+3


source


One solution might be to add group by u.id, u.language, u.program, u.status

since you are only fetching data from the user table.

But you may also want to consider redesigning your tables so that you can get a better solution for this problem, such as creating tables for languages ​​and programs.



[edit: fixed typo]

+4


source






More articles:


All Articles