Hello, i need help to make a procedure

the procedure is like this:

@cadastro_id INT

,@nome NVARCHAR(100)

,@grupo_id INT

SELECT DISTINCT

,b.cadastro_id

,b.cadastro_email

,b.cadastro_criado

FROM WMAIL_CADASTROS_GRUPOS a

INNER JOIN WMAIL_CADASTROS b

ON a.cadastro_id = b.cadastro_id

INNER JOIN WMAIL_GRUPOS c

ON a.grupo_id = c.grupo_id

AND (@grupo_id IS NULL OR a.grupo_id = @grupo_id)

AND ((b.cadastro_email like '%'+@nome+'%')

OR (b.cadastro_nome like '%'+@nome+'%'))

But it returns me the same e-mail 3 times, because that e-mail have in 3 different groups,

so how can i select distinct and select just one in the like, and just one each group i select?

if you want to get distinct field, you have to omit other fields like group in your select.

what do you want to do with this stored procedure?

lberan:

if you want to get distinct field, you have to omit other fields like group in your select.

what do you want to do with this stored procedure?

 I need to select the e-mails from a group, like i write on a textbox,

but how have e-mails is in other groups, like

E-mail: a@a.com GROUP: One,Two

And if i write a@a.com in the textbox, comes the email a@a.com double times, because it is in 2 groups differents,

i want to do a way that comes just 1 result i type.

you may use union like something as below:

select distinct email from firstTable

union

select distinct email from secondTable

union

select distinct email from thirdTable

where email not in (select distinct email from firstTable

union

select distinct email from secondTable )

@lberan,

Can you explain me better whad did you made on the sql code?

 Do not hava a way more simple, just adding things in my stored procedure?

atiam:

@lberan,

Can you explain me better whad did you made on the sql code?

 Do not hava a way more simple, just adding things in my stored procedure?

 

replace email in my query wiith your field name for email

replace firstTable, secondTable, thridTable with your table name.

to return the only distinct email,

SELECT DISTINCT b.cadastro_email  FROM WMAIL_CADASTROS_GRUPOS a
INNER JOIN WMAIL_CADASTROS b ON a.cadastro_id = b.cadastro_id
INNER JOIN WMAIL_GRUPOS c ON a.grupo_id = c.grupo_id
WHERE
a.cadastro_id =  ISNULL(@cadastro_id,a.cadastro_id) and
a.grupo_id = ISNULL(@grupo_id,a.grupo_id) AND
b.cliente_id = ISNULL(@cliente_id , b.cliente_id) AND
((b.cadastro_email like '%'+@nome+'%') OR (b.cadastro_nome like '%'+@nome+'%'))

But, i think you may wish to retrieve the other columns also, but i need a clarfication regarding that one. because you said that for one email, there are different groups, you wants to retrieve which group along with email,?

post whats your current output now and whats the output you required.

Liberan:

    i think union always give distinct results. so no need to apply distinct clause or not in clause. just that query

 select email from table1             union

select email from  table2            union

 select email from table3

just check this point. i just did some sample test.

ramireddyindia:

Liberan:

    i think union always give distinct results. so no need to apply distinct clause or not in clause. just that query

 

Thank you for correcting me.  You are right union automatically gives distinct results.  Here is a link with more information: http://en.wikipedia.org/wiki/Union_(SQL)