Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Red face Unanswered: Need Help With Query

    Here is the table structure:

    cdata_id
    users_id
    field_id
    value

    I am interested in only some of the fields value's for the users.

    Here is what I am using now:
    $query = "SELECT lm_users.users_id, lm_cdata.cfield_id, lm_cdata.value FROM lm_users
    INNER JOIN lm_cdata ON lm_users.users_id = lm_cdata.user_id WHERE lm_users.group_id =2
    AND (
    lm_cdata.cfield_id = '1'
    OR lm_cdata.cfield_id = '2'
    OR lm_cdata.cfield_id = '3'
    OR lm_cdata.cfield_id = '11'
    OR lm_cdata.cfield_id = '12'
    OR lm_cdata.cfield_id = '9'
    ) order by lm_users.users_id";

    But this means I have to do all of my own sorting for tree like structure in programming. Is there any way to sort this using SQL?

    This is how I wish to use the data from this query:
    Country 1 (cfield_id= 2) [Start with United States, then followed alpha by other countries]

    State 1 (cfield_id= 1)
    City (cfield_id= 3)
    Participant 1 (cfield_id= 9)
    event description (cfield_id= 12)
    Participant 2 (cfield_id= 9)
    event description (cfield_id= 12)
    City 2 (cfield_id= 3)
    Participant 3 (cfield_id= 9)
    event description (cfield_id=12)
    Participant 4 (cfield_id= 9)
    State 2 (cfield_id= 1)

    I have been struggling for several days on this. Any help or advice would be so much appreciated.

    Seeking sanity,
    KerKor

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kerkor
    I have been struggling for several days on this.
    i gave up after reading it three times

    i just don't understand what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Looks like some needs to catch up on the term 'NORMALIZATION' ...
    Don't store diffent kinds of data in one table/column ...
    That is, value is either a Country, a State, a City, a Participant ... but not all of them. So the problem lies in the database structure, not the query ... what you want to achieve with the given table structure is 'somewhat complicated'.

    ... at least, that what I kind of understand from it.

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •