Left Outer Join with no duplicates

[ToniS]

I would like to do the following select statement but to strip the duplicates,
this is what I have so far

SELECT E.ExhibitorID, E.ExhibitorShortName, E.ExhibitorName,
ES.ExhibitorShowID
FROM Exhibitors E
LEFT OUTER JOIN ExhibitorsShows ES ON E.ExhibitorID = ES.ExhibitorID


I have tried distinct and that did not work

Any ideas on what I am doing wrong?

 

[Sylvain Lafontaine]

SELECT DISTINCT ... maybe?

 

[ToniS]

I have tried select distinct E.ExhibitorID, E.ExhibitorShortName,
E.ExhibitorName,
ES.ExhibitorShowID
FROM Exhibitors E
LEFT OUTER JOIN ExhibitorsShows ES ON E.ExhibitorID = ES.ExhibitorID

this did not work, but If I did a select distinct (all columns except
ES.ExhibitorShowID) It worked.... Unfortunatly I need the ES.ExhibitorShowID

Basically an Exh can go to more then one show... I would like all of the exh
from the Exhibitors table along w/ the ES.ExhibitorShowID even if they are
not going to a show.

 

[Sylvain Lafontaine]

On which table are located these duplicates? Could you give an example?

 

[ToniS]

ExhibitorsShows (ES) has the duplicates

for example in the Exhibitor Table I have the following Exhibitors

Exh1
Exh2
Exh3

In the ES Table I have the following

Exh1 going to show1
Exh1 going to show2
Exh2 going to show2


Note Exh3 is not going to a show, therefore is not in the ES table....

I would like to see in my results

Exh1
Exh2
Exh3

right Now I am getting

Exh1
Exh1
Exh2
Exh3

 

[Sylvain Lafontaine]

Your explanation doesn't make any sense to me: if you have both show1 and
show2 going to Exh1, why do you want to see a single line for Exh1?

 

[ToniS]

it is a long story, I have a main form with several subforms on it... in some
cases I will need the ES.ExhbitorShowId for a some of the subform and others
I only need the E.ExhibitorID...... I am going to abort what I was trying to
do... (trying to do a select to have everything I needed and to handle
several different situations based on the users input - select all Exh or
select Exh going to Current Show ONLY) I was just informed of trying a main
form/subform/subform approach and I think this will work for me... I was
unaware you could have a subform w/i a subform...
sorry for wasting your time on this......

ToniS

 

[Robert Morley]

That's basically a conflict of interest. How can you get a distinct list of
exhibitors while still showing the ShowID?

To use your later example, if you have:

Exh1 Show1
Exh1 Show2
Exh2 Show1

What should it show?

If it's just Exh1, then why are you bothering with the Show at all?

If, on the other hand, you're looking for output something like:

Exh1 Show1
Show2
Exh2 Show1

....that's not doable directly from Access/SQL Server (AFAIK).


Rob

 

[ToniS]

You are right, I was basically trying to do a work around to a problem that I
had, The solution for me was to do a subform with in a subform and that fixed
my problem, therefore I do not need to do what I was trying to do w/ the
distinct command.......

 

[Robert Morley]

Great, glad to hear you were able to solve your problem. FYI, I *believe*
that two levels of subforms is the limit. I seem to remember that you can't
go deeper than that.


Rob