Title of the question is not clear, I'll try to describe it.
In my model I have the following :
A Network object includes Lines, and a Line object includes Routes (this is for public transportation).
Now I have a Subscription table where I handle user subscriptions for travelling on Networks, Lines or Routes :
╔════╦═════════╦══════╦═══════╗ ║ ID ║ Network ║ Line ║ Route ║ ╠════╬═════════╬══════╬═══════╣ ║ A ║ 1 ║ 6 ║ 12 ║ ║ B ║ 2 ║ 3 ║ NULL ║ ║ C ║ 1 ║ NULL ║ NULL ║ ╚════╩═════════╩══════╩═══════╝
That means :
- Subscription A is valid only on route 12
- Subscription B is valid on all routes of line 3
- Subscriptions C is valid on all routes of network 1.
I need to get all subscriptions allowed on route#12, i.e. I need to fetch first and third row.
What kind of request would allow me to do so please ?
In the end I'll be doing this in Java with JPQL, but I'd like to understand how to do it in plain SQL first.
Here's a SQL Fiddle in case anyone is willing to help.