2

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.

1

This is not the best way of writing this Query but this should do the trick.

SELECT
    *
FROM YourTable
WHERE ((Line IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
AND [Rout] IS NULL)
OR [Route] = 12)
OR (
Network IN (SELECT DISTINCT
    Network
FROM YourTable
WHERE [Route] = 12)
AND (LINE IS NULL
OR LINE IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
))

You can change those where conditions to joins. As you can see, its performing 3 select statements in IN Clause (one of which is a repetition) which may affect performance. Something like:

SELECT
    Main.*
FROM Subscription as Main
LEFT OUTER JOIN Subscription as Lines ON Lines.[Route] = 12
LEFT OUTER JOIN Subscription as Networks ON Networks.[Route] = 12
WHERE 
    Main.[Route] = 12
    OR (Main.Line = Lines.Line and Main.Route IS NULL)
    OR (Main.Network = Networks.Network AND (Main.Line IS NULL OR Main.Line = Lines.Line ))

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.