Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Location
    Saudi Arabia
    Posts
    80

    mysql select error

    hello

    what is the error in the follwing select

    Code:
    select regrecd_mobileid from dcsmsb_regrecordsdata where (regrecd_regrecid = '17' and regrecd_value = 'female')    and (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    table data is attached with the topic as pdf file


    if you look to the data table you will see the regrecd_mobileid 3 , while regrecd_regrecid = 17 and regrecd_value = female and regrecd_regrecid = 18 and regrecd_value = 1000-5000 , so it must be return if we do the above query

    I think by doing the query above will retuen 3 as regrecd_mobileid , but while doing the query there is no result

    any one know why?

    and if the sql statment is incorrect , any one can write the correct sql statment ?

    Thanks
    Attached Files Attached Files
    New to Servers World!

  2. #2
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    you're using
    Code:
    (regrecd_regrecid = '17' and regrecd_value = 'female')    and (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    mysql is trying to find something with all these conditions

    try this

    Code:
    (regrecd_regrecid = '17' and regrecd_value = 'female') OR (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    this way it will return 2 rows with values 2, 3

  3. #3
    Join Date
    Aug 2005
    Location
    Saudi Arabia
    Posts
    80
    Quote Originally Posted by Codebird View Post
    you're using
    Code:
    (regrecd_regrecid = '17' and regrecd_value = 'female')    and (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    mysql is trying to find something with all these conditions

    try this

    Code:
    (regrecd_regrecid = '17' and regrecd_value = 'female') OR (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    this way it will return 2 rows with values 2, 3


    yes you are sure in this case
    (
    Code:
    regrecd_regrecid = '17' and regrecd_value = 'female') OR (regrecd_regrecid = '18' and regrecd_value = '1000-5000')
    will return 2,3 as result

    but the condition is OR so the result will be 17=female or 18=1000-5000

    but i won't that

    i want to get the id of the persons that is 17=female AND 18=1000-5000

    is there any soluation?

    Thanks for replay
    New to Servers World!

  4. #4
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    well using what I said will give u the result u want try it out and look at your data I don't know either this is the way or what you want is impossible, they can't have 17 and 18 at same time neither they can have female and 1000-5000 at the same time so you query is so wrong

  5. #5
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    Great example of why you don't structure a database like this. If you want to keep that structure I think you'll need to join the table to itself, something like this (untested):
    Code:
    select t1.regrecd_mobileid
    from dcsmsb_regrecordsdata AS t1
    JOIN dcsmsb_regrecordsdata AS t2 ON t1.regrecd_mobileid = t2.regrecd_mobileid
    where (t1.regrecd_regrecid = '17' and t1.regrecd_value = 'female')
    and
    (t2.regrecd_regrecid = '18' and t2.regrecd_value = '1000-5000')
    But much better would be to structure your database to suit the data - the columns could be mobile_number, gender, etc. so you could simply:
    Code:
    SELECT mobile_number
    FROM better_structured_table
    WHERE gender = 'female'
    AND the_other_thing = '1000-5000'
    Last edited by foobic; 02-23-2008 at 07:46 PM. Reason: added 't1' to select
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  6. #6
    Join Date
    Aug 2005
    Location
    Saudi Arabia
    Posts
    80
    Quote Originally Posted by foobic View Post
    Great example of why you don't structure a database like this. If you want to keep that structure I think you'll need to join the table to itself, something like this (untested):
    Code:
    select t1.regrecd_mobileid
    from dcsmsb_regrecordsdata AS t1
    JOIN dcsmsb_regrecordsdata AS t2 ON t1.regrecd_mobileid = t2.regrecd_mobileid
    where (t1.regrecd_regrecid = '17' and t1.regrecd_value = 'female')
    and
    (t2.regrecd_regrecid = '18' and t2.regrecd_value = '1000-5000')
    But much better would be to structure your database to suit the data - the columns could be mobile_number, gender, etc. so you could simply:
    Code:
    SELECT mobile_number
    FROM better_structured_table
    WHERE gender = 'female'
    AND the_other_thing = '1000-5000'

    I use that query in this query

    Code:
    select mobileno from dcsmsb_mobiles where mobileid in (select DISTINCT regrecd_mobileid from dcsmsb_regrecordsdata where ( (regrecd_regrecid = '17' and regrecd_value = 'female') or (regrecd_regrecid = '18' and regrecd_value = '1000-5000')) and regrecd_mobileid in (select DISTINCT mobileid from dcsmsb_mobileswithsmsgroups where smsgroupid = '1' or smsgroupid='2' ) ) and mobileno like '96%'
    New to Servers World!

  7. #7
    Join Date
    Dec 2007
    Location
    Lebanon
    Posts
    413
    what???!!!! man use foobic's way

  8. #8
    Join Date
    Aug 2005
    Location
    Saudi Arabia
    Posts
    80
    Quote Originally Posted by foobic View Post
    Great example of why you don't structure a database like this. If you want to keep that structure I think you'll need to join the table to itself, something like this (untested):
    Code:
    select t1.regrecd_mobileid
    from dcsmsb_regrecordsdata AS t1
    JOIN dcsmsb_regrecordsdata AS t2 ON t1.regrecd_mobileid = t2.regrecd_mobileid
    where (t1.regrecd_regrecid = '17' and t1.regrecd_value = 'female')
    and
    (t2.regrecd_regrecid = '18' and t2.regrecd_value = '1000-5000')
    But much better would be to structure your database to suit the data - the columns could be mobile_number, gender, etc. so you could simply:
    Code:
    SELECT mobile_number
    FROM better_structured_table
    WHERE gender = 'female'
    AND the_other_thing = '1000-5000'

    I use

    Code:
    select t1.regrecd_mobileid
    from dcsmsb_regrecordsdata AS t1
    JOIN dcsmsb_regrecordsdata AS t2 ON t1.regrecd_mobileid = t2.regrecd_mobileid
    where (t1.regrecd_regrecid = '17' and t1.regrecd_value = 'female')
    and
    (t2.regrecd_regrecid = '18' and t2.regrecd_value = '1000-5000')
    and it work fine

    thank you very much foobic and thanks all
    New to Servers World!

  9. #9
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    You're welcome. But seriously - think about changing the database if you can. There are cases where a self-join is the right solution; this isn't one of them.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  10. #10
    Join Date
    Aug 2005
    Location
    Saudi Arabia
    Posts
    80
    hello this query

    Code:
    select t1.regrecd_mobileid
    from dcsmsb_regrecordsdata AS t1
    JOIN dcsmsb_regrecordsdata AS t2 ON t1.regrecd_mobileid = t2.regrecd_mobileid
    where (t1.regrecd_regrecid = '17' and t1.regrecd_value = 'female')
    and
    (t2.regrecd_regrecid = '18' and t2.regrecd_value = '1000-5000')
    is working when we have 2 variable (17 and 18) , but what if we have x varibale and we don't know how many the numbers of the variable when we are writing the script (e.g 17,18,19,20,21,......,x).

    is there any trick to that?

    Thanks
    New to Servers World!

  11. #11
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,842
    For each extra variable you have to join the table to itself again, so it rapidly becomes a hideous, wasteful, inefficient monstrosity. Which is why, as I may possibly have mentioned before, you shouldn't design your database like this!
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

Posting Permissions

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