Quickie for the SQL gurus

[judas]

I'm pulling a bunch of records from the database with the following query:
SELECT tblevents.EventID, tblevents.EventDate, TIME_FORMAT(tblevents.EventTime,'%H:%i') as EventTime,
tblworkshops.WorkshopName, tblvenues.VenueName, tblvenues.VenueTown, VenueCounty
FROM firststeps.tblevents
INNER JOIN tblworkshops ON tblevents.WorkshopID = tblworkshops.WorkshopID
INNER JOIN tblvenues ON tblevents.VenueID = tblvenues.VenueID
WHERE EventApproved = -1 AND TrainerConfirmed = -1 AND
tblvenues.VenueCounty LIKE 'Nottinghamshire' AND
tblevents.WorkshopID = 3 AND tblevents.EventDate >= '2004-01-29'

UNION
SELECT tblevents.EventID, tblevents.EventDate, TIME_FORMAT(tblevents.EventTime,'%H:%i') as EventTime,
tblworkshops.WorkshopName, tblvenues.VenueName, tblvenues.VenueTown, VenueCounty
FROM firststeps.tblevents
INNER JOIN tblworkshops ON tblevents.WorkshopID = tblworkshops.WorkshopID
INNER JOIN tblvenues ON tblevents.VenueID = tblvenues.VenueID
WHERE EventApproved = -1 AND TrainerConfirmed = -1 AND
tblvenues.VenueCounty <> 'Nottinghamshire' AND
tblevents.WorkshopID = 3 AND tblevents.EventDate >= '2004-01-29'

The problem I have is in ordering the records. I want all the records for 'Nottinghamshire' listed first and ordered by date, and the rest (<> 'Nottinghamshire' ordered by date only.

Can this be done with a single query? I can't use ORDER BY in each sub-query - shame as this would be an easy solution.

Ta!

[ATG]

why not use a single select instead of a union and have a column in the select which is the formular flim.flam = 'Nottinghma...'. This will be either true or false. Sort on that column first and then sort on date.

[judas]

Er... you've lost me there

[robbo1]

What about adding an extra column that's set to 1 if Nottinghamshire and 2 if not? You could then order by this extra column.
In SQL Server (for example) you could do this with a CASE statement.

[judas]

robbo1 said:

What about adding an extra column that's set to 1 if Nottinghamshire and 2 if not? You could then order by this extra column. In SQL Server (for example) you could do this with a CASE statement.

Ah... That's not gonna work. I'm using 'Nottinghamshire' as an example only. The actual value will be a querystring variable. D'oh

[delamars]

What application are using to do this ?

If it's microsoft access then just save the query and have another that queries the results of this query and does your ordering.

If it's SQL Server or similar then store the results in a temporary table and then select from that with your ordering etc.

[judas]

It's a web app using ASP/VBscript and a MySQL database.

[jam1et]

This should work:

SELECT 1 AS SortCol, tblevents.EventID, tblevents.EventDate, TIME_FORMAT(tblevents.EventTime,'%H:%i') as EventTime,
tblworkshops.WorkshopName, tblvenues.VenueName, tblvenues.VenueTown, VenueCounty
FROM firststeps.tblevents
INNER JOIN tblworkshops ON tblevents.WorkshopID = tblworkshops.WorkshopID
INNER JOIN tblvenues ON tblevents.VenueID = tblvenues.VenueID
WHERE EventApproved = -1 AND TrainerConfirmed = -1 AND
tblvenues.VenueCounty LIKE 'Nottinghamshire' AND
tblevents.WorkshopID = 3 AND tblevents.EventDate >= '2004-01-29'
UNION
SELECT 2 AS SortCol, tblevents.EventID, tblevents.EventDate, TIME_FORMAT(tblevents.EventTime,'%H:%i') as EventTime,
tblworkshops.WorkshopName, tblvenues.VenueName, tblvenues.VenueTown, VenueCounty
FROM firststeps.tblevents
INNER JOIN tblworkshops ON tblevents.WorkshopID = tblworkshops.WorkshopID
INNER JOIN tblvenues ON tblevents.VenueID = tblvenues.VenueID
WHERE EventApproved = -1 AND TrainerConfirmed = -1 AND
tblvenues.VenueCounty <> 'Nottinghamshire' AND
tblevents.WorkshopID = 3 AND tblevents.EventDate >= '2004-01-29'
ORDER BY SortCol, EventDate

[judas]

That does indeed do the trick! Thanks Jamie!

[jam1et]

No probs. Luvvvvverly pic on your profile btw!

[judas]

Thanks

While I'm here, another SQL quickie:

Any idea how to do the following:
I have a table full of people's information Names and e-mail address that I want to send e-mail newsletters to - let's call the columns: DelegateEmail and DelegateName. In some cases several delegates share an e-mail address. I only want to send the newsletters out once, but the following does not filter out duplicates as the names are different:

SELECT DISTINCT DelegateEmail, DelegateName from tbldelegates WHERE NewsletterOptOut = -1

Any ideas how to filter the records so that it only returns distinct email address, regardless of the rest of the data? Everything I've tried so far doesn't work

[TheHobbit]

judas said:

Thanks While I'm here, another SQL quickie: Any idea how to do the following: I have a table full of people's information Names and e-mail address that I want to send e-mail newsletters to - let's call the columns: DelegateEmail and DelegateName. In some cases several delegates share an e-mail address. I only want to send the newsletters out once, but the following does not filter out duplicates as the names are different: SELECT DISTINCT DelegateEmail, DelegateName from tbldelegates WHERE NewsletterOptOut = -1 Any ideas how to filter the records so that it only returns distinct email address, regardless of the rest of the data? Everything I've tried so far doesn't work

select distinct(DelegateEmail) from tbldelegates WHERE NewsletterOptOut = -1

will give a list of email addresses

or

select DelegateEmail,count(*) from tbldelegates WHERE NewsletterOptOut = -1 group by DelegateEmail

will also give a list of email address, but will show you a count of how many names have that email address.....

>> Edited by TheHobbit on Thursday 29th January 15:49

[judas]

Hmmm, not quite there...

I've tried this, but it won't work, but in may provide some clues:

Select DelegateEmail, DelegateFirstName from tbldelegates where
DelegateEmail in (Select Distinct(DelegateEmail) from tbldelegates where DelegateNewsletterOptOut = -1)

Any help?

[TheHobbit]

are there a mixture of cases? maybe that's whats throwing it. the first of the selects (with the "distinct") in it should do what you want, as long as the data doesn't have spaces, or capitalisation in it......

[judas]

TheHobbit said:

are there a mixture of cases? maybe that's whats throwing it. the first of the selects (with the "distinct") in it should do what you want, as long as the data doesn't have spaces, or capitalisation in it......

No - the e-mail addresses are case insensitive and pulling them out on their own gives the correct results. But as soon as I try pulling other data out with the e-mail addresses the records are no longer 'distinct', hence multiple records with the same e-mail address

[TheHobbit]

can you give me an example?
I'm having a bit of a day of MySQL as I'm trying to persuade CGI written in perl to go get a dataset from MySQL and then draw a png with GD::Graph and spit it at the browser.... *without* dropping it to a file on the filesystem......

[judas]

I'll leave you to your much harder problems I've got a way around my problem for now - bit of a kludge and not ideal, but it will do until I can get to the bottom of the SQL needed to do it properly.

Thanks for your suggestions though

[wiggy001]

judas said:

Thanks While I'm here, another SQL quickie: Any idea how to do the following: I have a table full of people's information Names and e-mail address that I want to send e-mail newsletters to - let's call the columns: DelegateEmail and DelegateName. In some cases several delegates share an e-mail address. I only want to send the newsletters out once, but the following does not filter out duplicates as the names are different: SELECT DISTINCT DelegateEmail, DelegateName from tbldelegates WHERE NewsletterOptOut = -1 Any ideas how to filter the records so that it only returns distinct email address, regardless of the rest of the data? Everything I've tried so far doesn't work

Try:

SELECT a.DelegateEmail, a.DelegateName from tbldelegates a WHERE NewsletterOptOut = -1 and a.DelegateName (select min(b.DelegateName) from tbldelegates b where a.<keys> = b.<keys>

[robbo1]

judas said:

Any ideas how to filter the records so that it only returns distinct email address, regardless of the rest of the data?

Depends which name you want returned with the email address. You can do a GROUP BY so that only distinct email addresses are returned, then you'll have to use an aggregate function (e.g. MIN, MAX) to return one of the names. Some SQL (e.g. Access) also allows FIRST and LAST.