September 27, 2007 at 3:02 pm
Ok, I have two Cross-Reference Tables called CustomerFlags and OrderFlags. These tables both have two columns, the Customer / Order ID and the FlagID. There is also another table storing the Flags.
What I need to do is return all Customers that have the same Flags as the Order. The Customer is allowed to have more Flags than the Order, however, they must at least have the Flags that are defined on the Order.
The only successful way I've been able to accomplish this is by creating a function that combines all the FlagIDs into a Bitmask and then strip the CustomerFlagMask from the OrderFlagMask and if it equals 0, then that customer has at least the same flags if not more.
There HAS to be a way to do this using JOINS but I just can't figure it out! We can't rely on this Bitmask thing working because eventually we'll have more than 64 Flags and then even a BigInt won't be able to handle this.
Anyone have ideas?
September 27, 2007 at 5:19 pm
I'm not sure I'm getting all of the nuances here, but this sounds like a counting game problem. It also sounds like there are relations you haven't which would make this work a little better.
I only have a foggy understanding of your DDL, but let's see if this doesn't point you in the right direction. If I'm way off-base - then try posting some more detail and/or try describing what you want with some more specifics.
Declare @minflagsneeded int
select @minflagsneeded = count(*) from flagsrequired --not sure how we get to the total # of flags needed but this would be your number.
--find the customerID's
select distinct customerID
from customerflag c inner join orderflag o on c.flagid=o.flagid
group by customerID, orderID
having count(*)=@minflagsneeded
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2007 at 6:47 pm
"What I need to do is return all Customers that have the same Flags as the Order. The Customer is allowed to have more Flagsthan the Order, however, they must at least have the Flags that are defined on the Order."
The question fits the pattern of a relational divide with remainders allowed. The remainder is that the customer can have more flags. Relational divide is not supported by SQL Server, but the question can
rephrased to "When the count of order flags for an order is equal to the count of flags in common between the order and the customer, the order qualifies"
Here are the two tables:
create table CustomerFlags
( CustomerId not null
, CustomerFlag not null
, constraint CustomerFlags_P primary key (CustomerId , CustomerFlag )
)
create table OrderFlags
( OrderId not null
, OrderFlag not null
CustomerId not null
, constraint OrderFlags_P primary key (OrderId , OrderFlag )
)
The SQL:
select OrderFlagSum.CustomerId , OrderFlagSum.OrderId
from (select OrderFlags.CustomerId , OrderFlags.OrderId , count(*) as OrderFlagCnt
from OrderFlags
group by OrderFlags.CustomerId , OrderFlags.OrderId
) as OrderFlagSum[/right]
join (select OrderFlags.CustomerId , OrderFlags.OrderId , count(*) as CustomerOrderCommonFlagCnt
from OrderFlags
join CustomerFlags
on OrderFlags.CustomerId= CustomerFlags.CustomerId
and OrderFlags.OrderFlag = CustomerFlags.OrderFlag
group by OrderFlags.CustomerId , OrderFlags.OrderId
) as CustomerOrderCommonFlagSum
on CustomerOrderCommonFlagSum.OrderId = OrderFlagSum.OrderId
and CustomerOrderCommonFlagCnt = OrderFlagCnt
SQL = Scarcely Qualifies as a Language
September 28, 2007 at 7:19 am
create table CustomerFlags
( CustomerId not null
, CustomerFlag not null
, constraint CustomerFlags_P primary key (CustomerId , CustomerFlag )
)
create table OrderFlags
( OrderId not null
, OrderFlag not null
CustomerId not null
, constraint OrderFlags_P primary key (OrderId , OrderFlag )
)
The SQL:
select OrderFlagSum.CustomerId , OrderFlagSum.OrderId
from (select OrderFlags.CustomerId , OrderFlags.OrderId , count(*) as OrderFlagCnt
from OrderFlags
group by OrderFlags.CustomerId , OrderFlags.OrderId
) as OrderFlagSum[/right]
join (select OrderFlags.CustomerId , OrderFlags.OrderId , count(*) as CustomerOrderCommonFlagCnt
from OrderFlags
join CustomerFlags
on OrderFlags.CustomerId= CustomerFlags.CustomerId
and OrderFlags.OrderFlag = CustomerFlags.OrderFlag
group by OrderFlags.CustomerId , OrderFlags.OrderId
) as CustomerOrderCommonFlagSum
on CustomerOrderCommonFlagSum.OrderId = OrderFlagSum.OrderId
and CustomerOrderCommonFlagCnt = OrderFlagCnt
Two possible corrections:
1. I think OP said that each of OrderFlags and CustomerFlags Table has only two columns, while your OrderFlags Table DDL has a CustomerID column.
2. You seem to be calling an CustomerFlags.OrderFlag column in your query when it is not there.
September 28, 2007 at 9:24 am
No, actually he's correct. There are only 2 columns on each table. I'm pretty sure I've already tried your suggestions but they didn't work 100% of the time. I'll try again and let you know.
October 1, 2007 at 6:38 am
Could you please post the DDL for these two tables?
(and perhaps also with a few sample rows that shows what the actual data look like)
I'm not quite getting how they are related with only two columns each, and also including the flag..?
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply