Conditional join?

I have a dropdown box in a .net app that I am populating from a couple
tables.
One is a salesrep table, with the sales rep code and a name in it.
The other table is a table with a sold to sales rep code and a ship to
sales rep code - which may not always be the same in both these columns.
One of those two columns will be in the salesrep table.
Is there a way to do a join on the first table from the second?
Basically, I want to pick out a statement like:
select repname from salesrep_table
inner join sourcetable on
sourcetable.soldtorepcode = salesrep_table.repcode or
sourcetable.shiptorepcode = salesrep_table.repcode
In other words, I want to show this repname in a box when the repcode
that is selected is in either the soldto repcode column, or the shipto
repcode table.
Wiil this work? If not, how would you go about doing this?
Any help would be appreciated.
BC>> I have a dropdown box in a .net app that I am populating from a couple ta
bles. <<
This is a database group and we do not care about the front end. The
basic principle of a tiered architecture is that display and input are
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.
This is a major screw up. There is no LOGICAL difference in these
guys. A sales rep is a sales rep; if you want to flag with a role,
then that goes into a column, not a separate table.
Look up "attribute splitting" as a design error.|||Hi Blasting Cap,
This help?
select repname
from salesrep_table as st
inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
t.shiptorepcode )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:OiYTzAWmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>I have a dropdown box in a .net app that I am populating from a couple
>tables.
> One is a salesrep table, with the sales rep code and a name in it.
> The other table is a table with a sold to sales rep code and a ship to
> sales rep code - which may not always be the same in both these columns.
> One of those two columns will be in the salesrep table.
> Is there a way to do a join on the first table from the second?
> Basically, I want to pick out a statement like:
> select repname from salesrep_table
> inner join sourcetable on
> sourcetable.soldtorepcode = salesrep_table.repcode or
> sourcetable.shiptorepcode = salesrep_table.repcode
> In other words, I want to show this repname in a box when the repcode that
> is selected is in either the soldto repcode column, or the shipto repcode
> table.
> Wiil this work? If not, how would you go about doing this?
> Any help would be appreciated.
> BC|||> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
Check the group title AGAIN -> MICROSOFT.SQLSERVER.PROGRAMMING
SQL Server is not just a database rather RDBMS - its a data processing
engine with many different features other than just SQL and store/retrieve.
If you don't like the posts then keep your gob shut and don't answer them!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151366508.537061.16560@.m73g2000cwd.googlegroups.com...
> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
>
> This is a major screw up. There is no LOGICAL difference in these
> guys. A sales rep is a sales rep; if you want to flag with a role,
> then that goes into a column, not a separate table.
> Look up "attribute splitting" as a design error.
>|||On 26 Jun 2006 17:01:48 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>This is a database group and we do not care about the front end. The
>basic principle of a tiered architecture is that display and input are
>done in the front end and never in the back end. This a more basic
>programming principle than just SQL and RDBMS.
The questions was not about how to program the front end, it was
simply about database retrieval. Mentioning the front end when
describing the query requirement and asking for help with the query is
not a problem.

>This is a major screw up. There is no LOGICAL difference in these
>guys. A sales rep is a sales rep; if you want to flag with a role,
>then that goes into a column, not a separate table.
>Look up "attribute splitting" as a design error.
Nonsense. This exact configuration occured where I worked, and was an
important BUSINESS REQUIREMENT. Surely you remember that the database
must model the business? The salesman who walked into the customer's
headquarters and made a sale that resulted in orders going to hundreds
of stores received credit for the sale. However, each store (shipto)
was also associated with a local salesman. Sales reporting required
tracking BOTH associations, the appropriate one chosen for any given
report.
Roy Harvey
Beacon Falls, CT|||Tony:
Thanks for the help..
It does what it is supposed to do - but not exactly what I want.
The table structure is sort of like this:
Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
Cust1 Rep1 Rep1 Grp1 Grp1
Cust2 Rep1 Rep2 Grp1 Grp2
Cust3 Rep1 Rep3 Grp1 Grp3
Cust4 Rep4 Rep4 Grp2 Grp2
Because Coalesce returns the first non-null value, it returns Rep1 in
the query when I actually want Reps 1, 2 & 3 to show up. I am going to
use this same thing when determining which groups I want to show up, too.
I believe if I reverse the columns (shipto first, soldto second), it may
do exactly what I wanted.
Many many thanks for your succinct help.
BC

> Hi Blasting Cap,
> This help?
> select repname
> from salesrep_table as st
> inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
> t.shiptorepcode )
> Tony.
>|||Hi BC,
If you get stuck just post another thread - good luck.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:O3esDoemGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Tony:
> Thanks for the help..
> It does what it is supposed to do - but not exactly what I want.
> The table structure is sort of like this:
> Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
> Cust1 Rep1 Rep1 Grp1 Grp1
> Cust2 Rep1 Rep2 Grp1 Grp2
> Cust3 Rep1 Rep3 Grp1 Grp3
> Cust4 Rep4 Rep4 Grp2 Grp2
> Because Coalesce returns the first non-null value, it returns Rep1 in the
> query when I actually want Reps 1, 2 & 3 to show up. I am going to use
> this same thing when determining which groups I want to show up, too.
> I believe if I reverse the columns (shipto first, soldto second), it may
> do exactly what I wanted.
> Many many thanks for your succinct help.
> BC
>
>