CODEWITHSUNDEEP
mysqlsql
Kiti
Kiti

Reputation: 543

How to retrieve a row where value is taken from other row in a same table (MYSQL)?

Ok, I have this table

ControlID - Version - Type - Rel ID  - Parent Rel ID
1         - 2       - 11   - 212     - 5
1         - 2       - 44   - 542     - 5
1         - 2       - 47   - 742     - 5

2         - 2       - 11   - 200     - 4
2         - 2       - 14   - 565     - 4
2         - 2       - 20   - 700     - 4

As showed in the above table, we have data in 2 blocks (Note: Rel ID is unique):

Block 1

ControlID - Version - Type - RelID  - ParentRelID
1         - 2       - 11   - 212     - 5
1         - 2       - 44   - 542     - 5
1         - 2       - 47   - 742     - 5

Block 2

ControlID - Version - Type - RelID  - ParentRelID
2         - 2       - 11   - 200     - 4
2         - 2       - 14   - 565     - 4
2         - 2       - 20   - 700     - 4

Ok, now user will enter any relID & the system will show the the Rel ID that has type=11 in the same block of entered relID.

Ex: if user enters 700, then it will know that 700 belongs to block 2 & then it will search the type=11 in block 2 & print out 200

Ex2: if user enters 742, then it will know that 742 belongs to block 1 & then it will search the type=11 in block 1 & print out 212

This is my query but I think it is too long & may be slow.

Select relID from mytable where  controlID = (select controlID from mytable where relID=700) 
and 
version= (select version from mytable where relID=700) 
and parentRelID= (select parentRelID from mytable where relID=700) 
and type=11

The above query works but too long & maybe slow, can you make it shorter & make it run faster?

Upvotes: 2

Views: 48

Answers (2)

abl
abl

Reputation: 5958

SELECT t1.RelId FROM table t1
    INNER JOIN table t2
    ON t1.ControlId = t2.ControlId
WHERE t2.RelId = 700
AND t1.type = 11

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

Why are you also selecting on version and ParentRelId ?? Do they also determine the Block? i.e., can there be different blocks that have the same RelID but have different versions and/or different parentRelIDs ?

If not, try this: 

Select * From table t
Where type = 11
   And ControlId =
        (Select ControlId 
         From table
         Where RelId = @RelId)

or ....

Select * From table t
Where type = 11
    and exists (Select * from table
                Where relId = @RelId
                  and ControlId = t.ControlId)

Upvotes: 1

Related Questions