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

[Nick Way]

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

[Guest]

You could read thru the first query and add the contents to a temp query assigning a sort by field and then read thru the second query and add it to the same temp query with a sort by field. Then do a query of that temp query.

[Dan_Bracuk]

I think a union query is the way to go. Use the sort by key mentioned by LogicBlast, but simply select it as a constant in your query. Then you don't to do any looping after you run your query.

[Newsgroup_User]

UNION all the way, with extra column for sorting:

<cfquery datasource="db" name="premium_listing_only" >
(select *, 1 AS sortcol
from tbl_listings
where advert_type = 1)
UNION ALL
(select *, 2 AS sortcol
from tbl_listings)
ORDER BY sortcol ASC
</cfquery>

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Nick Way]

Thanks guys, I really appreciate your help.

Azadi your post did the trick so that you very much indeed.

I'm not stuck on another problem which I hadn't forseen until implementing the new query..

now the search results look 'silly' when there is a recently added premium listing as that listing appears twice on the same page of results (once at the top and then again right next to it underneath)

i know this is what i asked for but i now realise i need to find some way of making it so the listing only appears twice *if the 2 listings won't appear on the same page as each other*

how i go about doing that im not sure, probably something to do with currentrow (must be at least 10 rows between them in order to display the 2nd listing or something [there are 10 listings per page of search results])

--my head hurts now!--

if anyone has any suggestions on this little teaser i'd be v grateful indeed

i can think of crude ways to do it but they'll gobble up the cpu im sure so im hoping to come up with a nice elegant (ideally sql) solution but alas my sql knowlege is very limited [eg this is the first time ive done a UNION]

TIA once again!

[Dan_Bracuk]

Assuming your id's are numeric and are all positive integers.

Create a list of 10 zeros. Let's call this TheList

Create a new query with the same fields and datatypes as the one you ran.

Loop through your first query. If the id field does not appear in TheList, append this row to your new query. At the end of the cfif block, delete the first element of TheList and append the current id value.

[Nick Way]

Dan you're a genius (as always!) thanks mate that's a nice little solution - just implemented it and it works a treat - thanks very much indeed.

thanks to everyone for their help - really appreciate

best regards

Nick