2
$\begingroup$

I'm trying to create a procedure in MySQL/MariaDB with optional filters for each participating table. Assuming the tables are $A, B, C, \ldots$, each table may or may not be filtered, and the result is that of each table having their respective optional filters applied. $A$ and $B$ have the relation A (1) -> (*) B, and similarly, B (1) -> (*) C and so on. (A (1) -> (*) B is the pseudo-ERD notation of one-to-many relation, where a member of $A$ has 0..* members of $B$ but a member of $B$ belongs to exactly 1 member of $A$)

With that, I've thought of the following function to filter & join each pair of tables:

/*
  F(): filter
  L: left side
  R: right side
*/

if(L.filter is NULL and R.filter is NULL) {
  return fullJoin(F(L), F(R))
} else if(L.filter not NULL and R.filter is NULL) {
  return leftJoin(F(L), F(R))
} else if(L.filter is NULL and R.filter not NULL) {
  return rightJoin(F(L), F(R))
} else { // both not NULL
  return innerJoin(F(L), F(R))
}

The filter function F() would be something like SELECT * FROM table WHERE input == NULL or input == column. The function is first applied to 4 tables as follows:

fn(fn(fn(A,B),C),D)

where the result of said function fn is considered to have a not NULL filter.

Additionally, I think the function fn can be packed into the following:

SELECT * FROM L FULL JOIN R 
WHERE (R.input == NULL || R.input = R.column)
AND (L.input == NULL || L.input = L.column)

Assuming my function is wrong (because it's the first draft), how is it flawed?

$\endgroup$
3
  • 1
    $\begingroup$ What is the context of this question & code? What is the notation "A (1) -> (*) B"? What is an "optional filter", why do A etc have those columns, what is the specification for your if, etc? $\endgroup$ – philipxy Apr 17 at 20:31
  • 1
    $\begingroup$ We discourage "please check whether my answer is correct" questions, as only "yes/no" answers are possible, which won't help you or future visitors. See here and here. Can you edit your post to ask about a specific conceptual issue you're uncertain about? As a rule of thumb, a good conceptual question should be useful even to someone who isn't looking at the problem you happen to be working on. If you just need someone to check your work, you might seek out a friend, classmate, or teacher. $\endgroup$ – D.W. Apr 18 at 2:48
  • $\begingroup$ I guess maybe "A (1) -> (*) B" is an ascii text version of a (pseudo-)ERD participation/FK "relationship" line with cardinality. Maybe involving some sort of PK id column. If so it's still not clear just what it is saying or what that has to do with whatever you are doing. $\endgroup$ – philipxy Apr 18 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.