Query Question

[umwoodm4]

Just a quick question regarding a problem I'm having with a query...

In it's shortest summary the problem is simply to narrow down a table that describes an N:N relationship to a 1:1 relation ship...

If the table in question looks like this :

------------------------
ID | userID | shiftID |
------------------------
0 0 A
1 1 A
2 0 B
3 1 B
------------------------

The query would ideally return the following :
------------------------
ID | userID | shiftID |
------------------------
0 0 A
3 1 B
------------------------
(or)
------------------------
ID | userID | shiftID |
------------------------
1 1 A
2 0 B
------------------------


So each "userID" will be distinctly associated with a single "shiftID" and vice versa (each "shiftID" will be distinctly associated with a single "userID")...

This seems like a really simple problem (and one that would be common place) but for whatever reason I can't seem to come up with the results I desire...


Thanks in advance for any help, I'm starting to suffer from this headache

-Brett

 

[rac2]

Is it actually an N:N relationship? Meaning each User is related to N Shifts and each Shift is related to N Users? What we would obtain from a cross-join, the Cartesian product.
Or do you simply mean many to many?

Cause if it truly is N:N then there can be a 1:1 set, otherwise not.

 

[umwoodm4]

Nice observation rac...I owe you an apology... the source table in question is not actually N:N, M:N more aptly describes the relationship between users and shifts.


At the tail end of your post you explained that since it is an M:N relationship I won't be able to reduce it to a 1:1 relationship with a single query. Is this fact?

 

[rac2]

Suppose M < N. This implies there will be some values on the N side that cannot be matched with values on the M side because all of those will be taken.

Suppose M > N. This implies there will be some values left over on the M side after matching up N of them.

Suppose M=N. What would such a match up mean? If the tables were designed to represent things that can match in any combination, what does it mean to place the two lists side-by-side (the 1:1 relationship)? What is the meaning in your data?

As to a query to obtain a result, I didnt actually talk about that. I have tried a couple of approaches without success. Supporting my feeling that such a result
would be non-sense even if it could be obtained.

 

[umwoodm4]

Hm, well first off it would probably most beneficial to explain what our data represents.

This table describes the relationship between a business' staff and the collection of shifts that must be worked in any given week. The reason that the table is an M:N relationship is a result of the fact that each staff member will have a different set of skills and will be only be able to work the shifts that correspond to their skill set.

So if our M:N relationship details the relationship of:
employees : shifts.

M>N - means there are more employees than shifts needed to be worked that day. (which is common practice in any workplace). And hence is acceptable.

M<N - means there are more shifts than employees, which is obviously not good, but for the sake of simplicity we'll assume this situation will never arise.

M=N - (the # of emploees is exactly equal to the # of shifts needed to be worked) will only arise in the greatest of coincidences so we should assume this will not happen as well.



So re-iterating once more, the table describes all the shifts that can be worked on any given day, and the employees who can work them.

Ideally what our query would return is a distinct listing of the shifts in the table with a distinct user assigned to "work" them. (No shift ID can appear twice and no userID can appear twice...thus reducing our M:N relationship to a 1:1 relationship).

I've recently starting playing with indexing and composite keys but still have yet to leverage anything successfully... I think my life expectancy has been shortened significantly since I've ran into this!

 

[r937]

since there are n factorial m to the power of uncertainty, how do you propose to assign each employee to a shift? who's on first? i dunno's on second, what's his name's on third

do not expect a solution from SQL here

apply your seniority rules, or you will get a union grievance

r937.com | rudy.ca

 

[umwoodm4]

n factorial m is a worst case scenario where every user could work every shift on every day, in our scenario this is far from true.

-Every user certainly cannot work every shift
-Every user cannot work on every day

Each user is assigned a shift from their collection, each shift is weighted equally (as long as it's in their collection)

 

[r937]

weighted? you never mentioned weighted

i still think you're not going to get an SQL solution...

r937.com | rudy.ca

 

[umwoodm4]

Thats because each shift is equally weighted ... ie NOT weighted.

Perhaps ignoring the context of the problem is essential here... at it's core the problem is to reduce and M:N relation ship to a 1:1 relationship with a single query... One would think this is a common database problem, but I guess not.