asked on

make SQL Server Query to prioritize / combine results from two other existing queries

Using SQL Server 2005, I have two working queries, and I'm trying to make a third that will prioritize them.

The first query (QA) pulls the top 5 results (never more than 5 provided) based only on UnitID. The second query (QB) pulls the top 9 results based on proximity (longitude and lattitude). Since I have 10 total slots to fill on my results table, no more or less, with the third query (QC) I'm trying to pull as many results as QA has (it will vary from 0 - 5), then fill in the leftover slots with results from QB. Existing queries follow:

QA:
<cfquery name="rsStatic" datasource="#application.constant.dsn#">
      SELECT l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL
</cfquery>

QB:
<cfquery name="rsDynamic" datasource="#application.constant.dsn#">
      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
      ORDER BY Distance
</cfquery>

QC: ??

jamcos

You can use Union for the 2 statements and select the top from that result. You probably need to add Width and Height as columns from QB to make the columns match. It would look like:
Select top 10 * from (<QA> union all <QB>) as unionedresult

Here's a specific example I tested on a database structure I have locally that returned all the query A results and as many query B results as needed to make a total of 10:
select top 10 * from (
select * from v_preflist where ownerid = '1295'
union all
select top 10 * from v_preflist where ownerid = 'co63') as myresult

Upekrithen

ASKER

I've tried your suggestion with the results posted after this comment, and I think one reason it doesn't work is that I'm already using parentheses, so the code breaks after the first internal closing parentheses. Also, isn't there a way to do a query on queries so that I could simply reference the other two queries? Anyway, here is my non-working result from trying to implement your suggestion. Thanks!

<cfquery name="rsCombined" datasource="#application.constant.dsn#">
      SELECT TOP 10 FROM (
      SELECT TOP 5
            l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
      WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
            AND p.PictID IS NOT NULL

      UNION ALL

      SELECT TOP 10
            l.*, p.PictType, p.PictID, p.Extension,
            CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
                  ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
                  END AS Distance
      FROM Listings AS l
            LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
      WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
            AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
            AND p.PictID IS NOT NULL
            
      ORDER BY Distance)
</cfquery>

jamcos

I had the same thought about just selecting ... for whatever reason, SQL Server is picky and makes you name the parenthesized query ('as myresult' in my sample). I also noticed you are missing * in select top 10. I didn't setup a sample db to match your structure, but I believe this will work:

SELECT TOP 10 * FROM (
SELECT TOP 5
l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height
FROM Listings AS l
LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'SCPN' OR p.PictType = 'FCPN'
WHERE l.UnitID = <cfqueryparam value="#Form.UnitID#" cfsqltype="cf_sql_bigint">
AND p.PictID IS NOT NULL

UNION ALL

SELECT TOP 10
l.*, p.PictType, p.PictID, p.Extension,
CASE WHEN ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">) < ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
THEN ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
ELSE ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
END AS Distance
FROM Listings AS l
LEFT OUTER JOIN Pictures AS p ON l.ListingID = p.ListingID AND p.PictType = 'NCPN'
WHERE 1.0 > ABS(Latitude - <cfqueryparam value="#rsStation.Latitude#" cfsqltype="cf_sql_float">)
AND 1.0 > ABS(Longitude - <cfqueryparam value="#rsStation.Longitude#" cfsqltype="cf_sql_float">)
AND p.PictID IS NOT NULL

ORDER BY Distance) as myresults

Upekrithen

ASKER

Well, copied and pasted your code (basic difference being yes, the asterisk, plus two parentheses, so apparently the parentheses were not the problem), and I get this error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

ASKER CERTIFIED SOLUTION

jamcos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Upekrithen

ASKER

Cool! I think that does it, testing aside. Good job!! Thanks for your quick response, and more importantly, your accuracy. Thanks!

- J.