If...Else syntax problems -- sql server mseq

Michael Fitzpatrick
12/16/2003 10:34:13 AM

Vishal,

Thanks for the help thus far. Here is, in detail, what I
am trying to accomplish and where I stand currently, with
your help.
Goal:
If contact X is a member of contactcategories.category and
has an address.addresstype = 'home' select the
corresponding address.addrlines. If not, pull the
address.addrlines connected to the address.addresstype
labeled 'Mailing'. There can only be one address per
contact in the results, but all contacts associated with
the list must be represented.

Table Structure:
Tables:
Contacts
contactCategories
Address

Relationships
Contacts.contacts = address.contacts
address.contacts = contacts.contacts
ContactCategories.contacts = contacts.contacts
contacts.contacts = contactcategories.contacts

1:Contact X must be a member of contactcategory 'Holiday
#1'
(contact.contact = contactcategory.contact)

2:If you do a search for contact X in address
(address.contacts), and contact X has multiple addresses
listed, all will show up in results...from that list if
there is an address.addresstype = 'Home' Select the
corresponding Address Lines (address.addrlines)

3:If Contact X doesn't have a address.addresstype
= 'Home', then it will have an address.addresstype
= 'Mailing'...select the address.addrlines accordingly.

Only one address should be selected per contact regardless
of how many address.addresstypes they have, and I only am
concerned with the ones labeled Home and Mailing.

Where I stand....
With the 'if exists'... query you suggested, I am only
getting 59 results....all of them are the 'Home'
addrlines. The list contains 89 contacts so I am missing
30 'Mailing' contacts and their addrlines. The query is
currently:

if exists(select a.addresstype from contacts c,
contactcategories cc,address a
where c.contacts = cc.contacts and cc.category = 'Holiday
#1' and a.addresstype = 'Home'
and a.contacts = c.contacts)
begin
select c.fullname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c,
contactcategories cc,address a
where c.contacts = cc.contacts and cc.category = 'Holiday
#1' and a.addresstype = 'Home'
and a.contacts = c.contacts
end
else
Begin
select c.fullname, c.compname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c, contactcategories cc,
address a where c.contacts = cc.contacts and cc.category
= 'Holiday #1' and a.addresstype = 'Mailing'
and a.contacts = c.contacts
end

If you have any additional insight into this problem I
would greatly appreciate any and all help. If I left out
any portion of pertinent info please advise and I will
fill in the gaps accordingly. Thanks in advance.

Regards,

Mike Fitzpatrick

[quoted text, click to view]

Vishal Parkar
12/16/2003 9:26:16 PM

I think you are looking for following select statement.

Try:

select c.fullname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c,contactcategories cc,address a
where c.contacts = cc.contacts and a.contacts = c.contacts
and cc.category = 'Holiday #1'
and a.addresstype = 'Home'
union all
select c.fullname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c,contactcategories cc,address a
where c.contacts = cc.contacts and a.contacts = c.contacts
and cc.category = 'Holiday #1'
and a.addresstype = 'Mailing'
and not exists
(select * from contacts x,contactcategories y,address z
where x.contacts = y.contacts and z.contacts = x.contacts
and y.category = 'Holiday #1'
and x.addresstype = 'Home'
and x.contacts = c.contacts)

Vishal Parkar
12/16/2003 10:00:12 PM

Michael,

see my comments inline.

if exists(select a.addresstype from contacts c,
contactcategories cc,address a
where c.contacts = cc.contacts and cc.category = 'Holiday #1' and a.addresstype = 'Home'
and a.contacts = c.contacts)
/*
if (select a.addresstype from contacts c,
contactcategories cc,
address a where c.contacts = cc.contacts and cc.category
= 'Holiday #1' and a.addresstype = 'Home'
and a.contacts = c.contacts) = 'Home'
*/
-- above is not a valid t-sql syntax. use if exists... syntax as i've shown above.
-- therefore if above query returns at least one row then following query will be
-- executed or it will jump into ELSE part.

begin

/*
select c.fullname, c.compname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c, contactcategories cc,address a
*/
-- above query will not give you required result set and will generate a cross product
-- because you are not using join.probably following is what you are looking for.

select c.fullname, c.compname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c, contactcategories cc,address a
where c.contacts = cc.contacts and a.contacts = c.contacts
end
else
Begin
select c.fullname, c.compname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c, contactcategories cc,
address a where c.contacts = cc.contacts and cc.category
= 'Holiday #1' and a.addresstype = 'Mailing'
and a.contacts = c.contacts
end

[quoted text, click to view]

above error is not generated because of any of the query that you've posted. It does not
have any subquery. look for such a query where subquery is used and make sure it will
return only one row at a time.

--
- Vishal

P.S. always pls post relevent table structure/sample data and expected result set to get
quick and accurate solution.

Miuchael Fitzpatrick
12/17/2003 4:00:54 PM

Vishal,

This is it exactly. It worked great. Thanks for your
time and assistance. I am now trying to process what
union does, how it works and where its applicable. If you
have the time and energy, I do have a question: In your
query why does the last part (see below) work...Select *
from contacts...etc, specifically. Is it pulling off of
the temporary table created by the union command, if so
how does SQL know to use the temp table and not the 'real'
tables called contacts, etc...

Thanks again for your help, I truly appreciate it.

Regards,

Mike Fitzpatrick

[quoted text, click to view]

Vishal Parkar
12/17/2003 6:51:06 PM

let me explain you this query step-by-step

-- Part 1
select c.fullname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c,contactcategories cc,address a
where c.contacts = cc.contacts and a.contacts = c.contacts
and cc.category = 'Holiday #1'
and a.addresstype = 'Home'
union all
-- Part 2
select c.fullname, cc.category, substring
(a.addrlines,3,a.addrlinessize)as myfield, a.addresstype
from contacts c,contactcategories cc,address a
where c.contacts = cc.contacts and a.contacts = c.contacts
and cc.category = 'Holiday #1'
and a.addresstype = 'Mailing'
and not exists
-- Part 2(a)
(select * from contacts x,contactcategories y,address z
where x.contacts = y.contacts and z.contacts = x.contacts
and y.category = 'Holiday #1'
and x.addresstype = 'Home'
and x.contacts = c.contacts)

-- Part1
Lets assume Part1 and Part2 are independent queries which returns the same number of columns and each column's datatype
is same in this case UNION ALL will just merge the output of both the queries and will return the resultset as if its
an outcome of single SQL statement.

-- Part2 / Part2(a)
Part2 Query is a subquery with NOT EXISTS clause, Part2(a) is just a check for an existance of the rows that has been
checked by the outer query of Part2, therefore "select * from...." subquery will return true or false and outer query
will return the row accordingly.

In the query or part2, the logic will be something as follows. because of not exists clause all the rows whose addresstype
is not "Home" will be returned and the resulting rows will be filtered for addresstype = "Mailing"

HTH

Michael Fitzpatrick
12/19/2003 11:56:12 AM

Vishal,
Thank you very much for the help, especially so for the
explaination it helped clear up a lot of questions. I do
appreciate all of your time, effort and knowledge.

Best Regards,

Mike

[quoted text, click to view]
returns the same number of columns and each column's
datatype
[quoted text, click to view]
(a) is just a check for an existance of the rows that has
been
[quoted text, click to view]
from...." subquery will return true or false and outer
query
[quoted text, click to view]
follows. because of not exists clause all the rows whose
addresstype
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : If...Else syntax problems