CODEWITHSUNDEEP
sqlmariadb
TeeDeJee
TeeDeJee

Reputation: 3741

One query with subquery that uses data of the main one

id lft rgt company
10 1 7 1
11 2 3 1
12 5 6 1
13 8 9 1
14 1 2 2
14 3 4 2

I want to write a query that gets e.g. the row with 10 + row 11/12 based on the other columns of the first row. Think my example query might make more sense.

SELECT * FROM organisation o 
WHERE o.id = 10 and o.company = 1
OR o.id IN (SELECT id FROM organisation WHERE lft > o.lft AND lft < o.rgt and company = 1);

Is this possible in 1 query. Or do I first get row 10, then do another query to get the other 2 rows based on the lft/rgt values.

What I want is get row 10 or rows where lft is between lft and rgt from the row(s) that I want.

But if I try this then I only get row with id 10.

lft is only unique per company, so not unique for the entire table

Upvotes: 0

Views: 33

Answers (3)

D-Shih
D-Shih

Reputation: 46219

I would like to use self join to get result.

Query 1:

SELECT o2.* 
FROM organisation o 
INNER JOIN organisation o2
  ON (o2.lft > o.lft AND o2.lft < o.rgt) OR o2.id = o.id
WHERE o.id = 10  and o.company = 1

Results:

| id | lft | rgt | company |
|----|-----|-----|---------|
| 10 |   1 |   7 |       1 |
| 11 |   2 |   3 |       1 |
| 12 |   5 |   6 |       1 |

Upvotes: 1

Nick
Nick

Reputation: 147166

This can be done with two queries, one to get the row with id = 10 and one to get the other rows which meet your condition, using a JOIN to evaluate the condition. We can UNION the two queries together to get all the results in one query:

SELECT *
FROM organisation o 
WHERE o.id = 10
UNION ALL
SELECT o2.*
FROM organisation o
JOIN organisation o2 ON o2.lft > o.lft AND o2.lft < o.rgt
WHERE o.id = 10

Output:

id  lft     rgt
10  1       7
11  2       3
12  5       6

Demo on dbfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269733

If your bounds are inclusive, then you can simply do:

SELECT o.*
FROM organisation o 
WHERE EXISTS (SELECT 1
              FROM organisation o2
              WHERE o2.id = 10 AND o2.company = 1 AND
                    o2.lft <= o.lft AND
                    o2.rgt >= o.rgt
             );

If the bounds are exclusive, then you need a second explicit comparison, because the row does not match itself:

SELECT o.*
FROM organisation o 
WHERE o.id = 10 AND o2.company = 1 OR
      EXISTS (SELECT 1
              FROM organisation o2
              WHERE o2.id = 10 AND o2.company = 1 AND
                    o2.lft < o.lft AND
                    o2.rgt > o.rgt
             );

Upvotes: 0

Related Questions