June 17, 2003 at 11:52 am
I have a simple query with two joins, returning a client name and all listed billing addresses. If there are no addresses that are listed as type Billing, then it returns the one set as Primary. While I can get the query to return all Billing AND all primary, I want it to return just Billing and if there are no billing then return the primary.
Currently the query that returns both is:
SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.* FROM Clients C
INNER JOIN InvoiceHeader I ON I.ClientID=C.ClientID
INNER JOIN Addresses A ON A.ClientID=C.ClientID
WHERE I.InvoiceNr=@InvoiceNr AND (A.AddrTypeID=@BillingAddressID OR A.PrimaryAddr=1)
Is it possible in TSQL to get the join to stop if there are any A.AddrTypeID matching BillingAddressID?
June 17, 2003 at 12:35 pm
I'm not quite sure what you mean by get the join to stop...could you elaborate?
June 17, 2003 at 12:37 pm
There can be 0 to Many Addresses of Type BillingAddrType.
There must be and can be only 1 PrimaryAddr
If there is 1 or More Billing Addresses, it would return all of them.
If no billing addresses, it returns the Primary Address
June 17, 2003 at 12:40 pm
Should it return the Primary Address even if there are billing address(es)?
June 17, 2003 at 12:42 pm
No, if there is 1 or more Billing, then do not return the Primary.
The current query as shown, returns them all.
June 17, 2003 at 12:48 pm
OK, try this:
SET NOCOUNT ON
SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.* FROM Clients C
INNER JOIN InvoiceHeader I ON I.ClientID=C.ClientID
INNER JOIN Addresses A ON A.ClientID=C.ClientID
WHERE (I.InvoiceNr=@InvoiceNr AND A.PrimaryAddr=1)
IF @@ROWCOUNT = 0 BEGIN
SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.* FROM Clients C
INNER JOIN InvoiceHeader I ON I.ClientID=C.ClientID
INNER JOIN Addresses A ON A.ClientID=C.ClientID
WHERE (I.InvoiceNr=@InvoiceNr AND A.AddrTypeID=@BillingAddressID)
END
SET NOCOUNT OFF
June 17, 2003 at 12:54 pm
Ah, of course - I had not thought of using RowCount.
Thanks!
June 17, 2003 at 1:00 pm
Here's an alternative:
IF EXISTS
(
SELECT *
FROM Clients C
INNER JOIN InvoiceHeader I
ON I.ClientID=C.ClientID
INNER JOIN Addresses A
ON A.ClientID=C.ClientID
WHERE I.InvoiceNr=@InvoiceNr
AND A.PrimaryAddr=1
)
BEGIN
SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.* FROM Clients C
INNER JOIN InvoiceHeader I
ON I.ClientID=C.ClientID
INNER JOIN Addresses A
ON A.ClientID=C.ClientID
WHERE I.InvoiceNr=@InvoiceNr
AND A.PrimaryAddr=1
END
ELSE BEGIN
SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.*
FROM Clients C
INNER JOIN InvoiceHeader I
ON I.ClientID=C.ClientID
INNER JOIN Addresses A
ON A.ClientID=C.ClientID
WHERE I.InvoiceNr=@InvoiceNr
AND A.AddrTypeID=@BillingAddressID
END
HTH,
Jay
June 17, 2003 at 2:57 pm
Not to one up Jay here (sorry joke about another thread). But here is another alternative.
SELECT
COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName,
A.*
FROM
Clients C
INNER JOIN
InvoiceHeader I
ON
I.ClientID=C.ClientID
INNER JOIN
Addresses A
ON
A.ClientID=C.ClientID AND
(CASE WHEN A.AddrTypeID=@BillingAddressID THEN 1 ELSE A.PrimaryAddr END) = 1
WHERE
I.InvoiceNr=@InvoiceNr
The only then that confused me was what
A.AddrTypeID
values are and what
@BillingAddressID
would be. Might have a better example of the code above if I knew that. But I have used this logic before on conditional matching.
June 18, 2003 at 3:11 am
quote:
SQL does not support conditional joins.Best is to work set-based
First SELECT returns records for billing address.
Second SELECT returns records where no billing address is available, so primary address is returned
SELECT
COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName,
A.*
FROM
Clients C
INNER JOIN
InvoiceHeader I
ON
I.ClientID=C.ClientID
INNER JOIN
Addresses A
ON
A.ClientID=C.ClientID AND
A.AddrTypeID=@BillingAddressID /* Billing Address available */
WHERE
I.InvoiceNr=@InvoiceNr
UNION ALL
SELECT
COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName,
A.*
FROM
Clients C
INNER JOIN
InvoiceHeader I
ON
I.ClientID=C.ClientID
INNER JOIN
Addresses A
ON
A.ClientID=C.ClientID AND
A.AddrTypeID NOT IN (SELECT A.AddrTypeID FROM Addresses S_A WHERE S_A.AddrTypeID = @BillingAddressID)
/* No address is available
AND A.PrimaryAddr =1 /* Primary Address */
WHERE
I.InvoiceNr=@InvoiceNr
June 18, 2003 at 4:04 am
quote:
SQL does not support conditional joins.Best is to work set-based
First SELECT returns records for billing address.
Actually SQL does allow conditional joing between two tables for instance if TABLE1 was joing to TABLE2 but it could be that TABLE2 has two potential columns Col1 and Col2 that could be joined but Col2 should only be used if Col1 is null you can do
TABLE1.COLX = ISNULL(TABLE2.COL1,TABLE2.COL2)
You cannot however conditionally join between two different tables to a single as easy. But with a bit of logic you can get it done. In this case thou it is simply making sure it decides if a particular condition is met. Either it only has a Primary Address or it has a list of Billing Addresses but do not include the Primary Address. The problem is I need to know the potential values of A.AddrTypeID to know for sure how to avoid.
Remember ON is a WHERE cluase in itself.
Example
CREATE TABLE pMain (
idx int not null primary key,
val varchar(10) not null
)
GO
CREATE TABLE pBill (
FK_idx int not null,
Addr varchar(40) not null,
IsBill char(1) null,
PrimAddr bit not null
)
GO
INSERT pMain (idx, val) values (1,'Test')
INSERT pMain (idx, val) values (4,'David')
INSERT pMain (idx, val) values (5,'Martin')
INSERT pBill (FK_idx, Addr, PrimAddr) values (1,'123 Main', 1)
INSERT pBill (FK_idx, Addr, IsBill, PrimAddr) values (1,'124 Jones Ave', 'Y', 0)
INSERT pBill (FK_idx, Addr, IsBill, PrimAddr) values (1,'124 Terrance Way', 'Y', 0)
INSERT pBill (FK_idx, Addr, IsBill, PrimAddr) values (4,'11 Martin Shore Drive', 'N', 1)
INSERT pBill (FK_idx, Addr, IsBill, PrimAddr) values (4,'11 Walter Reeves Blvd', 'Y', 0)
INSERT pBill (FK_idx, Addr, PrimAddr) values (5,'145 Main Ave', 1)
GO
SELECT
*
FROM
pMain
INNER JOIN
pBill
ON
pMain.idx = pBill.FK_idx
WHERE
(CASE
WHEN pBill.PrimAddr = 1 AND pBill.FK_idx IN (SELECT iB1.FK_idx FROM pBill iB1 WHERE iB1.IsBill = 'Y') THEN 0
ELSE 1 END) = 1
GO
It is all a matter of finding the right condition to control it within 1 statement. In fact I have gotten rid of some long UNION queries with some simple but seemingly complicated joing like this.
June 18, 2003 at 7:37 am
My Thanks for all the great replies.
Any one of which will accomplish my task.
While I am using the RowCount check, the other examples, especially the last one utilzing an index is something I can put to use in another situation.
Great site and I truly appreciate the help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply