SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stuck on a conditional query

    Hi,

    I am trying to get two columns from one table deoendent on a value in a third column. if the value sought in col3 is not there, then the data with the alternative value should be returned.

    Code MySQL:
    SELECT 
           condition_name
         , condition_details
         FROM table 
         WHERE id = ?
         or id = '1'

    As it is, it brings in values for when both conditions are true.

    just a nudge please, I am sure I am missing a mysql term that would do it.

    bazz

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try adding this:

    ORDER BY FIELD(id, ?, 1)
    LIMIT 1

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dan, thanks for the reply.

    doesn't do it though. I'm reading up on CASE and IFNULL to see if they might work.

    bazz

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OPK, I have read up a bit more and am back to an outer join. still can;t seem to work out how to get data from business_id 1, when data for business_id>1 is null.

    Code MySQL:
    SELECT 
               bt.condition_name
             , bt.condition_details
      FROM booking_terms bt 
       left outer 
      join booking_terms bt2
          on bt.condition_name = bt2.condition_name
        and bt2.business_id = '1'
        and bt.business_id = ?

    If I change the last 'and' to where, it prevents to optional nature of the query and alwways outputs only data when business_id = 1.

    surely I don't need a second query if the first result is null?

    bazz

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm having trouble understanding your question. can you provide some sample data for both cases, and the desired output?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok the query is to get two columns where business id = say 234 and if that returns null, then it needs to get the data where business_id = 1

    sample data

    | condition name | condition details | business_id |
    | Cancellation Policy | blurb about cancellation policy | 234|
    | Cancellation Policy | blurb about cancelation policy | 1 |
    | Smoking Policy | blurb about smoking polcy | 1 |

    desired output@

    Cancellation Policy ~ blurb about cancellation policy
    Smoking Policy ~ blurb about smoking policy

    The point to note (I suppose) is that when business_id = 234, both its own cancellation policy as well as the smoking policy for business 1, shall be outputted.

    If there was a smoking policy entered for 234, then it would output instead of that for business 1.

    ps. you aren't too far from my partner's son. he's in sarasota at Simon's.
    bazz

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    duh, what's wrong with this solution --

    1. run query for id=234
    2. if no results, run query for id=1

    k.i.s.s.

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Blimey, I thought that would be inefficient because it involves two queries. I thought/was hoping, there would be a way to do it in one.

    I'll try that and see how I get on.

    Thanks
    bazz

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Why doesn't my answer work?

    Code:
    mysql> select * from tmp;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | Dan  |
    |    2 | Joe  |
    |    3 | Rob  |
    +------+------+
    
    mysql> select id, name from tmp where id = 2 or id = 1 order by field(id, 2, 1) limit 1;
    +------+------+
    | id   | name |
    +------+------+
    |    2 | Joe  |
    +------+------+
    
    mysql> select id, name from tmp where id = 5 or id = 1 order by field(id, 5, 1) limit 1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | Dan  |
    +------+------+
    Isn't that the desired result? If the id you searched for is there, it returns that, else it returns the row for id 1.

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, maybe it is more convoluted than I thought.

    Dan your plan works if there is just one record for each business. However, there are several records for each business ao limiing to 1 means only the first of say 5, shows. Yes it does deal with the alternative aspect but only if there is one record to be displayed.

    bazz

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason there are spearate records for differing policies is so that the visitor gets headings relative to each point.

    bazz

  12. #12
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I got it working.

    Thanks for your tips and pointers guys. much appreciated.

    bazz


Bookmarks

Posting Permissions

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