6 Replies Latest reply on Feb 27, 2009 5:53 AM by happysailingdude

    do i need a UNION / or a JOIN or something else?

    happysailingdude Level 1
      i have a table which contains property listings

      each property listing can either be a "standard" or "premium" depending on how much the advertiser has paid to display their advert

      this data is stored in the "advert_type" column (advert_type =1 means advert is premium / advert_type = 0 means advert is standard)

      when displayed the premium listings appear at the top of the list

      currently we handle this simply by "order by advert_type, date_advert_placed" - nice and simple

      however i want to make the premium listings appear twice (once at the top and once where they would naturally appear in the list if they were "standard", so I'm thinking I need to do 2 queries and then join them somehow

      the important thing is this query gets hit hard and the table involved is big so I'm looking for efficiency here

      im thinking my 2 queries would be something like

      <cfquery datasource="db" name="premium_listing_only" >
      select *
      from tbl_listings
      where advert_type = 1
      </cfquery>

      <cfquery datasource="db" name="all_listings" >
      select *
      from tbl_listings
      where advert_type = 0 or advert_type = 1
      </cfquery>

      I could outout the first query and then the second, but of course I need to do some record set paging so I'm really looking for one query..

      All suggestions gratefully received

      TIA