I need to select everything in the first column that contains test1 then after that I need to find e

I need to select everything in the first column that contains test1 then after that I need to find everything that matches whatever is in column 2

what my data looks like


first column second column

test1 454
other 461
other 349
other 339
other 343
other 342

what i need to show

test1 454
other 461


Any pointers or help would be appreciated.

 

Do you have a unique autoincrement ID for each row there? Or can you add one?

 

yes I do.

 

So you have it like

ID NAME VALUE
100 test1 454
101 other 461
102 other 349
103 other 339
104 other 343
105 other 342

and you want to find NAME "test1" and its VALUE 454 on row with ID 100
and then you want the following row, i.e. the one with ID 101?

 

Sorry I just realized I posted some of this incorrectly..

ID NAME VALUE value2
100 test1 454 349
101 other 461 345
102 other 349 341
103 other 339 249
104 other 343 149
105 other 342 849

I need to run a query where I find test1 then where other matches value2 from value. I think its a query inside a query? I would search for (test1) and other but where they match on value and value2.

so my results would be

100 test1 454 349
102 other 349 341

 

Ah, now it makes sense

this is like

SELECT something
FROM table
WHERE value2 = (SELECT value FROM table WHERE name = 'test1') X

 

Yea that looks like its it. I had no idea you could do a query inside a query like that. thank you.

 

You can even do something like

SELECT
        MAX(X) BestResult
FROM (
        SELECT
                AVG(Score) X
        FROM (
                SELECT
                        Score, UserID
                FROM Result
        ) a
        GROUP BY (UserID)
) b;

Code (markup):

by which I mean that you can select FROM a subquery.

However, be careful with subqueries as their performance may be poor.