Complex Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Complex Join

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-25 : 09:54:13

I need to do somthing a little Strange.

I need to collect data from a table in both a joined and unjoined condition.

The table contains shipping "from" address customers (we do this as a blind or 3rd party shipment for many distributors) we need to querry all the ship from address for this customer but also include our defalt Address.

Now the kicker Since this is to be used in a pull down I cannot pass though peramiters I can only select by ref to a value in the ref table based upon the NT userID

Something like this.

SELECT StockSLS.dbo.tblArShipFrom.ShipFromName, StockSLS.dbo.tblArShipFrom.Addr1, StockSLS.dbo.tblArShipFrom.City,
StockSLS.dbo.tblArShipFrom.ShipfromId

(FROM StockSLS.dbo.tblArShipFrom INNER JOIN
Ref_user_JobCust ON StockSLS.dbo.tblArShipFrom.CustId = Ref_user_JobCust.CustID
WHERE (Ref_user_JobCust.UserID = SUSER_SNAME(SUSER_SID()))

OR

(FROM StockSLS.dbo.tblArShipFrom
WHERE (StockSLS.dbo.tblArShipFrom.CustId = N'CCC001')

I can do one or the other, how the heck can I do both.

Jim
Users <> Logic

SmileyConspiracy
Starting Member

8 Posts

Posted - 2003-11-25 : 10:11:57

Can you use UNION ALL to do something like this?

SELECT StockSLS.dbo.tblArShipFrom.ShipFromName, StockSLS.dbo.tblArShipFrom.Addr1, StockSLS.dbo.tblArShipFrom.City,
StockSLS.dbo.tblArShipFrom.ShipfromId
FROM StockSLS.dbo.tblArShipFrom INNER JOIN
Ref_user_JobCust ON StockSLS.dbo.tblArShipFrom.CustId = Ref_user_JobCust.CustID
WHERE (Ref_user_JobCust.UserID = SUSER_SNAME(SUSER_SID())
UNION ALL
SELECT StockSLS.dbo.tblArShipFrom.ShipFromName, StockSLS.dbo.tblArShipFrom.Addr1, StockSLS.dbo.tblArShipFrom.City,
StockSLS.dbo.tblArShipFrom.ShipfromId
FROM StockSLS.dbo.tblArShipFrom
WHERE (StockSLS.dbo.tblArShipFrom.CustId = N'CCC001')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 10:18:17

is this what you want?



SELECT StockSLS.dbo.tblArShipFrom.ShipFromName,   
  StockSLS.dbo.tblArShipFrom.Addr1, StockSLS.dbo.tblArShipFrom.City, 
  StockSLS.dbo.tblArShipFrom.ShipfromId
FROM 
  StockSLS.dbo.tblArShipFrom 
INNER JOIN
  Ref_user_JobCust 
ON 
  StockSLS.dbo.tblArShipFrom.CustId =  Ref_user_JobCust.CustID
WHERE 
  (Ref_user_JobCust.UserID = SUSER_SNAME(SUSER_SID())) 
  OR
  (StockSLS.dbo.tblArShipFrom.CustId = N'CCC001')

??

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-25 : 10:33:08

quote:
Originally posted by SmileyConspiracy
Can you use UNION ALL to do something like this?

May be but Not with That Syntax.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-25 : 10:35:16

quote:

Originally posted by jsmith8858

is this what you want?



SELECT StockSLS.dbo.tblArShipFrom.ShipFromName,   
  StockSLS.dbo.tblArShipFrom.Addr1, StockSLS.dbo.tblArShipFrom.City, 
  StockSLS.dbo.tblArShipFrom.ShipfromId
FROM 
  StockSLS.dbo.tblArShipFrom 
INNER JOIN
  Ref_user_JobCust 
ON 
  StockSLS.dbo.tblArShipFrom.CustId =  Ref_user_JobCust.CustID
WHERE 
  (Ref_user_JobCust.UserID = SUSER_SNAME(SUSER_SID())) 
  OR
  (StockSLS.dbo.tblArShipFrom.CustId = N'CCC001')

??

- Jeff

I thought that too Jeff
But the Inner Join Locks Me out of the Default Address

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-11-25 : 11:00:06

Ok Got It

SELECT StockSLS.dbo.tblArShipFrom.ShipFromName, StockSLS.dbo.tblArShipFrom.Addr1,
StockSLS.dbo.tblArShipFrom.City, StockSLS.dbo.tblArShipFrom.ShipfromId
FROM StockSLS.dbo.tblArShipFrom
INNER JOIN Ref_user_JobCust ON
StockSLS.dbo.tblArShipFrom.CustId = Ref_user_JobCust.CustID
WHERE (Ref_user_JobCust.UserID = SUSER_SNAME(SUSER_SID()))
UNION
SELECT StockSLS.dbo.tblArShipFrom.ShipFromName, StockSLS.dbo.tblArShipFrom.Addr1,
StockSLS.dbo.tblArShipFrom.City, StockSLS.dbo.tblArShipFrom.ShipfromId
FROM StockSLS.dbo.tblArShipFrom
WHERE (CustID = 'ccc001')

Thanks Smiley and Jeff Gess my brain needed a kick start.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources