SQL help please!

[judas]

I'm trying to work out the correct SQL to query a two fields in each of four tables in a database and combine all the results into a single result, but as it's two days before xmas my brain just ain't up to it

Each of the four tables contains a field for an e-mail address and a true/false field to indicate whether they've signed up for a newsletter.

The table.field designations are:
tblcompanies.CompanyContactEmail
tblcompanies.CompanyNewsletterOptOut
tbldelegates.DelegateEmail
tbldelegates.DelegateNewsletterOptOut
tblhosts.HostEmail
tblhosts.Host.NewsletterOptOut
tbltrainers.TrainerEmail
tbltrainers.TrainerNewsletterOptOut

Is there any way of querying the above to dump all the email address into a single 'email' result when each of the ***NewsletterOptOut values are -1.

[liszt]

judas said:

I'm trying to work out the correct SQL to query a two fields in each of four tables in a database and combine all the results into a single result, but as it's two days before xmas my brain just ain't up to it Each of the four tables contains a field for an e-mail address and a true/false field to indicate whether they've signed up for a newsletter. The table.field designations are: tblcompanies.CompanyContactEmail tblcompanies.CompanyNewsletterOptOut tbldelegates.DelegateEmail tbldelegates.DelegateNewsletterOptOut tblhosts.HostEmail tblhosts.Host.NewsletterOptOut tbltrainers.TrainerEmail tbltrainers.TrainerNewsletterOptOut Is there any way of querying the above to dump all the email address into a single 'email' result when each of the ***NewsletterOptOut values are -1.

Yep
Just need to union the select statements together

Select CompanyContactEmail
from tblcompanies
where
CompanyNewsletterOptOut = -1

Union Select DelegateEmail
from tbldelegates
where
DelegateNewsletterOptOut= -1

Union Select HostEmail
from tblhosts
where
HostNewsletterOptOut = -1

Union Select TrainerEmail
from tbltrainers
where
TrainerNewsletterOptOut = -1;

[pdV6]

I concur

[liszt]

pdV6 said:

I concur

I thank you

[judas]

Brilliant! Many thanks I knew it had to be a union, but the past few days of pre-xmas madness had taken its toll

Thanks guys!

[pdV6]

Just a quick point, though, you do seem to be selecting the email addresses for all the companies that have opted OUT of receiving the newsletter...

Intentional?

[judas]

Intentional? Yes. Change of spec halfway through development, so people opt in rather than out. As I'm the only person to ever have to delve around in the code/database it wasn't worth changing, especially with the amount of feature creep going on and keeping me busy!

[liszt]

judas said:

feature creep

Thats a phrase I haven't heard in a while.

I am currenly suffering from Feature pissing off into the distance at a vast rate of knots.

Still, I am working my notice at the moment so it will be my bosses job to try and deliver what I have promised everybody

[judas]

liszt said:

Still, I am working my notice at the moment so it will be my bosses job to try and deliver what I have promised everybody

[pdV6]

judas said:

Change of spec halfway through development

What is this "spec" thing you're talking about?
Doesn't seem to be a recognised phrase where I am at the moment...

[judas]

That's what I keep wondering too. 'Spec' is an internal working term, which refers to the various impossible, contradictory and ever changing requirements as dreamt up by the client on whatever planet they happen to be at the time and then 'translated' via the boss into something like 'whatever keeps the client happy (ie, paying the bills)'.