June 20, 2008 at 3:02 am
Hello, I am using the following SQL statement to pull back some data, but I am having problems with extra records that I don't need.
Basically, some contacts have the ROLE of "MAIN" and "PREX", but I don't want those to come out - I only want those contacts with the ROLE of "MAIN" only to come out.
I guess that part of my WHERE clause is wrong, but I can't work out what it is!
Thanks for your help
Jon
SELECT org.organisation_number, org.name, addr.address, addr.town, addr.county,
addr.postcode, org.status, c.label_name, cp.position, m.notes
FROM communications m RIGHT OUTER JOIN contact_roles cr on m.contact_number = cr.contact_number
INNER JOIN organisations org on cr.organisation_number = org.organisation_number
INNER JOIN contacts c on cr.contact_number = c.contact_number
INNER JOIN addresses addr on addr.address_number = org.address_number
INNER JOIN organisation_categories orgc on orgc.organisation_number = org.organisation_number
INNER JOIN contact_positions cp on cp.contact_number = c.contact_number
WHERE cr.role In ('MAIN') AND cr.role != ('PREX') AND cp.mail = 'Y' AND (org.status In ('BRAN','FULL'))
and orgc.activity = 'SUBSID' and orgc.activity_value = 'YES'
ORDER BY org.name
June 20, 2008 at 3:37 am
WHERE cr.role In ('MAIN') AND cr.role != ('PREX') AND cp.mail = 'Y' AND (org.status In ('BRAN','FULL'))
should be
WHERE cr.role = ('MAIN') AND cp.mail = 'Y' AND (org.status In ('BRAN','FULL'))
You shouldn't need to write an (=) statement and a corresponding (Not=).
One should be enough
June 20, 2008 at 3:42 am
Hello,
Thanks for your help. I've just re-read my initial post and I don't think I've explained myself fully... sorry!
Some contacts have a role of just "MAIN".
Some contacts have roles of "MAIN" and "PREX".
I only want those contacts to be retrieved where they have a role of "MAIN" only.
Currently, my statement is correctly retrieving everyone who has "MAIN" but is also effectively retrieving those who have "MAIN" and "PREX"....
Thanks
Jon
June 20, 2008 at 3:48 am
Okay makes sense, do your contacts have a unique indentifier?
June 20, 2008 at 3:52 am
Thank you....
Yes, contacts can be identifield on contact_number. This identifier is available in both the contacts and the contact_roles tables.
Jon
June 20, 2008 at 4:02 am
I would do something like this
WHERE cr.role = ('MAIN') and cr.contact_number not in(select contact_number from Contract_Roles where cr.role= "PREX")
AND cp.mail = 'Y' AND (org.status In ('BRAN','FULL'))
The subquery (select contact_number from Contract_Roles where cr.role= "PREX") is the key to not selecting the Contact_numbers that have a "PREX"
June 20, 2008 at 4:10 am
"some contacts have the ROLE of "MAIN" and "PREX", but I don't want those to come out - I only want those contacts with the ROLE of "MAIN" only to come out."
As the table definitions have not been posted, you have made answering your question very difficult, but here is a guess.
Simplification of the posted SQL:
SELECT ...
FROM communications m RIGHT OUTER JOIN contact_roles cr on m.contact_number = cr.contact_number
WHERE cr.role In ('MAIN') AND cr.role != ('PREX')
Regarding the SQL, the usage of two character abbreviations for the table names makes me translate the abbreviations to the real table name, which adds to my confusion. Some of the proposed naming standards, especially by Joe Celko, state that a table alias should follow the same naming standard as tables and I agree with this standard.
As all SQL Editors have advance capability including drag and drop, replace all and there are also many formating tools, the format you are using is very confusing. You are probably using Enterprise Manager, which produces this type of format. Try using SQL Query Analyzer instead. The code indentation as posted is different that when the same code appears in Query Analyzer, so please bear with the strangness.
Reformated SQL:
SELECT ...
FROM communications
RIGHT OUTER JOIN
contact_roles
on contact_roles.contact_number = communications.contact_number
WHERE contact_roles.role In ('MAIN') AND contact_roles.role != ('PREX')
Taking the requirements individually
First - Contacts that have a role of "Main" becomes:
SELECT ...
FROM communications
JOIN contact_roles
on contact_roles.contact_number = communications.contact_number
WHERE contact_roles.role = 'MAIN'
Second - contacts the do NOT have also have a role of "PREX"
SELECT ...
FROM communications
JOIN contact_roles
on contact_roles.contact_number = communications.contact_number
WHERE contact_roles.role = 'MAIN'
ANDNOT EXISTS
(SELECT1
FROMcontact_roles as contact_roles_PREX
WHEREcontact_roles_PREX.contact_number = communications.contact_number
ANDcontact_roles_PREX.role = 'PREX'
)
That is my final guess.
SQL = Scarcely Qualifies as a Language
June 20, 2008 at 4:17 am
Hello,
I think that might well have worked!
Thanks to both of you for your help.
Jon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy