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.

Building a query

Discussion in 'Databases' started by wizzard, Oct 27, 2007.

  1. #1
    Hello,

    I have some troubles building a query.

    I have a table profiles with the fields (id, property, value) and the rows are like this =>

    jim,language,de
    jim,gender,male
    jim,email,emailaddress@....

    Now i want to filter on the property and my query is like this =>

    SELECT * FROM profile WHERE property='language' AND value='de' AND property='gender' AND value='male'

    But it's not working its showing the wrong language.

    Can anyone help me ?

    Cheers,
    Kris
     
    wizzard, Oct 27, 2007 IP
  2. mhaye_01

    mhaye_01 Banned

    Messages:
    395
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #2
    select * from profile where (property='language' OR property='gender') AND
    (value='de' OR value='male')

    when you use the operator AND it means both data/value must both true..
     
    mhaye_01, Oct 27, 2007 IP
  3. kjewat

    kjewat Active Member

    Messages:
    149
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #3
    I think you would need to change the query a little bit, or else you could in theory get a row where property='language' and value='male'...

    select * from profile where (property='language' AND value='de') OR
    (property='gender' AND value='male')
     
    kjewat, Oct 28, 2007 IP
  4. wizzard

    wizzard Peon

    Messages:
    160
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Well this query is still showing female genders and other language speaking people.
     
    wizzard, Oct 29, 2007 IP
  5. jcoc07

    jcoc07 Peon

    Messages:
    30
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    You need something like this:

    select * from profile where
    (property='language' AND value='de')
    AND (property='gender' AND value='male') /* for each property */
     
    jcoc07, Oct 29, 2007 IP
  6. delhi_wala

    delhi_wala Peon

    Messages:
    75
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #6
    It should be select * from profile where
    (property='language' AND value='de')
    OR
    (property='gender' AND value='male')
     
    delhi_wala, Oct 29, 2007 IP
  7. Kuldeep1952

    Kuldeep1952 Active Member

    Messages:
    290
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    60
    #7
    You require to use a self-join since the values are in different records:

    select a.id from profile a, profile b
    where (a.id = b.id and
    a.property='language' AND a.value='de' and
    b.property='gender' AND b.value='male')
     
    Kuldeep1952, Oct 30, 2007 IP
    kjewat likes this.