Retrieve record for past year if current year does not exist - no dups

[aliashippysmom]

Hi! I have an exhibitor database for an conference. I want to display all exhibitors currently signed up to exhibit for 2006. But if they signed up in 2005, and not in 2006 yet, I want to display these in the same list.

SELECT * FROM Exhibitor
WHERE exhibit_year = '2006'

is what I had until I was tasked to add 2005 exhibitors that are not in 2006.

The following query works, but I get duplicate exhibitor names:

SELECT * FROM Exhibitor
WHERE exhibit_year IN ('2006','2005')
ORDER BY exhibitor_name

This works, too:

SELECT distinct exhibitor_name FROM Exhibitor
WHERE exhibit_year IN ('2006','2005')
ORDER BY exhibitor_name

I want to do something like this, but it does NOT work:

SELECT distinct exhibitor_name, exh_id FROM Exhibitor
WHERE exhibit_year IN ('2006','2005')
ORDER BY exhibitor_name

BUT I need to have the key field retrieved as well, because as the exhibitors are displayed the user can click on the name to get more information. I can write two queries to do this, by using the distinct one that works, but is there a way to write just one query? Can't use distinct for that, since all the keys are naturally distinct. I hope this makes sense. Can anyone help? Thanks in advance.

 

[rac2]

It sounds like getting more information depends on exh_id. Presumably the information will be different for different exh_id even though the exhibitor name is the same. Therefore which information do you wish to get? The info from 2006 or the info from 2005?

That question aside, do you possibly have different tables for exhibitors and sign-ups? If not you may wish to re-organize the data. Or maybe not. But this would sure be a lot easier if you had an exhibitor_id which uniquely referred to exhibitors. It appears that exh_id is more like an exhibitor_at_a_conference_id.

 

[SQLDenis]

create table Exhibitor (exhibit_year int,exhibitor_name varchar(50),exh_id int)
insert into Exhibitor
select 2005,'bla1',1 union all
select 2006,'bla1',2 union all
select 2005,'bla2',3 union all
select 2005,'bla3',4 union all
select 2006,'bla4',5

SELECT e1.exhibitor_name, e1.exh_id,e1.exhibit_year
from Exhibitor e1 join(
select Max(exhibit_year) as MaxYear,exhibitor_name
FROM Exhibitor
WHERE exhibit_year IN ('2006','2005')
group by exhibitor_name) z on e1.exhibitor_name = z.exhibitor_name
and e1.exhibit_year = z.MaxYear

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[adamroof]

i hope this makes sense, i feel for you...
unique customers with recent history. each customer has many histories.

SELECT t3.ContactID, t1.HistType, t1.HistNotes, t1.HistDate
FROM hs_tblHistory t1
INNER JOIN hs_tblContact t3 on t1.ContactID = t3.ContactID
WHERE t1.HistDate > getDate() - 3
AND t1.HistDate = (select max(t2.HistDate) from hs_tblHistory t2 where t2.contactID = t1.contactID)
ORDER BY HistDate DESC

 

[aliashippysmom]

Hey, thanks for your reply. exh_id is the key for the Exhibitor table and that table contains exhibitors for all conference years! I need to retrieve the information for exhibitors from 2006 first of all. Then I need to retrieve the exhibitors that exhibited in 2005 and have not signed up for 2006. Here's a query I wrote, which works, but probably is not the most "elegant" solution:

SELECT exhibitor_name, exh_id FROM Exhibitor
WHERE exhibitor_year = '2006'
UNION
SELECT exhibitor_name, exh_id FROM Exhibitor
WHERE exhibitor_year = '2005' AND
exh_name NOT IN (SELECT exh_name FROM Exhibitor
WHERE exhibitor_year = '2006')
ORDER BY exh_name

Unfortunately, there is only one table for Exhibitors. We have never had to retrieve the exhibitors in this way before. Usually, we just display the exhibitors for the current year and don't have to worry about mixing the years up on one page of the site. Thanks for helping me sort this out. Other suggestions get the brain thinking in a different direction sometimes!

 

[SQLDenis]

add

order by e1.exhibit_year desc ,e1.exhibitor_name

at the bottom off the code I poster earlier

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/