Match Column Data in MYSQL

Discussion in 'MySQL' started by glasglow, Jul 10, 2007.

0
  1. #1
    I have this type of data:

    [RowOneword] [Data1] [Randomnumber7]
    [RowTwoword] [Data1] [Randomnumber19]
    [RowThreeword] [Data1] [Randomnumber47]
    [RowFourword] [Data1] [Randomnumber6]
    [RowFiveword] [Data1] [Randomnumber7]
    [RowSixword] [Data1] [Randomnumber3]

    The query I would like to do is match the third column where it says [Randomnumber] with a given value. Here I will give the value [Rowfiveword].

    By giving it that value I would like the query to give me back [Rowfiveword] AND give me [RowOneWord] as well. Since they both have a third column match [Randomnumber7]

    The closest explanation I've come to it is JOIN but it's all an one table and these are columns, not different tables.. but I'm sure I'm over reading it anyway.

    SELECT * FROM TABLE WHERE KEYWORD= "[Rowfiveword]" AND Randomnumber=randomnumber..

    but I have to take the keyword out so it gives me all the rows where the third column matches and I'm not sure how to do that.
     
    glasglow, Jul 10, 2007 IP
  2. glasglow

    glasglow Active Member

    Messages:
    926
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #2
    So far I got this but he problem I am having is if the "keyword" is a phrase it only searches and matches the first part of the phrase and not the whole phrase.

    $query="SELECT * FROM table WHERE keyword='$keyword' AND column1='1' UNION SELECT * FROM sametable WHERE column2=column2";
     
    glasglow, Jul 10, 2007 IP
  3. glasglow

    glasglow Active Member

    Messages:
    926
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #3
    So far I've tried ALL of these queries..

    SELECT * FROM tablename WHERE keyword='a priori' AND position='1' AND (SELECT relationkey FROM tablename WHERE relationkey = (SELECT relationkey FROM tablename))

    SELECT * FROM tablename WHERE keyword='a priori' AND position='1' IN (SELECT * FROM tablename WHERE relationkey = (SELECT relationkey FROM tablename))

    SELECT * FROM tablename WHERE (relationkey=relationkey) AND keyword='a priori' AND relationkey = '1';

    SELECT * FROM tablename WHERE relationkey IN (SELECT * FROM tablename WHERE keyword = 'a priori' AND position = '1');

    SELECT * from tablename WHERE relationkey IN (SELECT relationkey FROM tablename WHERE keyword='a priori' AND position='1');

    SELECT * FROM tablename WHERE keyword LIKE 'a priori' AND position='1' IN (SELECT relationkey from tablename WHERE relationkey=relationkey)

    SELECT relationkey from tablename WHERE relationkey=relationkey IN (SELECT keyword FROM tablename WHERE keyword LIKE 'a priori' IN (SELECT position FROM tablename WHERE position='1'))

    SELECT * FROM tablename WHERE keyword LIKE 'a priori' AND position='1' IN (SELECT * FROM tablename WHERE relationkey ANY (SELECT relationkey FROM tablename))

    SELECT * FROM tablename WHERE EXISTS
    (SELECT * FROM tablename WHERE keyword LIKE 'a priori' AND position LIKE '1');

    SELECT * FROM tablename WHERE EXISTS
    (SELECT * FROM tablename WHERE keyword RLIKE 'a priori' AND position LIKE '1');

    SELECT * FROM tablename WHERE relationkey IN
    (SELECT * FROM tablename WHERE keyword LIKE 'a priori' AND position LIKE '1');

    SELECT * FROM tablename WHERE relationkey IN
    (SELECT * FROM tablename WHERE keyword RLIKE 'a priori' AND position LIKE '1');

    SELECT * FROM tablename WHERE relationkey IN (SELECT * FROM tablename WHERE keyword LIKE 'a priori' AND position='1')

    SELECT * FROM tablename WHERE relationkey IN (SELECT keyword FROM tablename WHERE keyword RLIKE 'a priori' AND position='1')

    SELECT * FROM tablename WHERE relationkey MATCH (SELECT keyword FROM tablename WHERE keyword RLIKE 'a priori' AND position='1')

    SELECT * FROM tablename WHERE relationkey IN (SELECT keyword FROM tablename WHERE keyword LIKE 'a priori')

    SELECT * FROM tablename WHERE relationkey = ANY (SELECT relationkey FROM tablename) AND (SELECT keyword FROM tablename WHERE keyword LIKE 'a priori') AND (SELECT position from tablename WHERE position='1');
     
    glasglow, Jul 10, 2007 IP