Need help with if exists syntax 1

[smatthews]

Using SQL2k. Can someone please help. An individual can have more than one record in the Name_Address table. If there is a record with purpose ='publish' I need to return that record, otherwise the record that has preferred_mail =1 Only want one record per ID.

n.id,
n.full_name
FROM name n INNER JOIN member_info mi
ON n.id = n.id JOIN name_address na
ON na.id = n.id
AND n.ID = mi.ID
WHERE (na.purpose = 'publish'
or na.preferred_mail ='1')

Thanks, Smatthews

 

[kjv1611]

It looks like you lopped off some of your code when you copied and pasted it to your question...

So is this what you have?

SELECT n.id, n.full_name
FROM name n
    INNER JOIN
  member_info mi
    ON n.id = n.id
    JOIN
  name_address na
   ON (na.id = n.id) AND
      (n.ID = mi.ID)
WHERE (na.purpose = 'publish') OR
      (na.preferred_mail ='1')

)

With it like that, I'll take a swing at the solution in just a moment..

 

[kjv1611]

Before I get into it, I'll say this - I think you can fix it perfectly bu using a UNION statement, and take the OR out altogether..

I'll have what I'm talking about here just shortly..

 

[kjv1611]

Also, your joins are a bit messed up... I'll get that sorted out in my stab at a solution...

 

[kjv1611]

Well, it still may not be perfect, but here goes. Your might also want to look at your joins.. for instance, do you still want to return records when there is no record in Address, but there is in Name, by chance? Anyway, that's just something to think about. For the time being, see if this works:

SELECT	n.id, n.full_name
FROM	name n
			INNER JOIN
		member_info mi
			ON n.id = mi.id
			JOIN
		name_address na
			ON	(na.id = n.id) 
WHERE	(na.purpose = 'publish')
UNION
SELECT	n.id, n.full_name
FROM	name n
			INNER JOIN
		member_info mi
			ON n.id = mi.id
			JOIN
		name_address na
			ON	(na.id = n.id) 
WHERE	(na.preferred_mail ='1')

 

[smatthews]

Thanks kjv1611 for your help.

My code was only halfway there.

What I was actually looking for is:
If there is a "publish" record, return that one, or else return the preferred_mail = 1

I am only look for one record per n.id There will always be a preferred_mail = 1 records, there may or may not be a 'publish' record.

Thanks, smatthews

 

[gmmastros]

For each id, can there be multiple rows with purpose = "publish" or preferred_mail = 1? If there can be multiple rows, how will you know which one to use?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

No,there can only be one 'publish' record and/or one prefered_mail = 1 record per n.id but, one n.id can have both records. If there is a publish record per n.id I want to return that one, if there isn't I want to return the preferred_mail =1. Everyone has a preferred_mail = 1 record.

Sorry for the confusion.

 

[kjv1611]

Hmm, I think... maybe... but maybe not.. not tested...

Instead of just UNION, try UNION ALL, and see if you get the results you're looking at. I think it'll eliminate dups that way. Of course, I'm not sure if it has any order involved for which of the dup records it'll include...

Actually, there may be a better way - not tested, but it's a thought..

Okay, you've got the UNION statement... you need to add an additional criteria, or maybe a couple different UNIONS...

So, you can have one that says... if it's published, then include it regardless in one SELECT statement.

Then in another statement, you can say, include it if it's NOT published, but DOES have a preferred_mail = 1 record...

Will something like that work? Sorry, just typing as I think, so probably a little scattered..

 

[smatthews]

I think I understand what you are saying...giving it a try now...thanks!

 

[smatthews]

That worked...thanks, have a star