Question
do i need a UNION / or a JOIN or something else?
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
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
