1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'MySQL' started by xbat, Feb 25, 2015.

  1. #1
    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.
     
    Solved! View solution.
    xbat, Feb 25, 2015 IP
  2. O-D-T

    O-D-T Member

    Messages:
    180
    Likes Received:
    10
    Best Answers:
    3
    Trophy Points:
    43
    #2
    Do you have a unique autoincrement ID for each row there? Or can you add one?
     
    O-D-T, Feb 26, 2015 IP
  3. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #3
    yes I do.
     
    xbat, Feb 28, 2015 IP
  4. O-D-T

    O-D-T Member

    Messages:
    180
    Likes Received:
    10
    Best Answers:
    3
    Trophy Points:
    43
    #4
    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?
     
    O-D-T, Feb 28, 2015 IP
  5. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #5
    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
     
    xbat, Feb 28, 2015 IP
  6. #6
    Ah, now it makes sense :)

    this is like

    SELECT something
    FROM table
    WHERE value2 = (SELECT value FROM table WHERE name = 'test1') X
     
    O-D-T, Feb 28, 2015 IP
  7. xbat

    xbat Well-Known Member

    Messages:
    326
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    105
    #7
    Yea that looks like its it. I had no idea you could do a query inside a query like that. thank you.
     
    xbat, Feb 28, 2015 IP
  8. O-D-T

    O-D-T Member

    Messages:
    180
    Likes Received:
    10
    Best Answers:
    3
    Trophy Points:
    43
    #8
    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.
     
    O-D-T, Feb 28, 2015 IP