[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

SQL Remove ResultSet C from (TableA LEFT JOIN TableB ResultSet)

Simplified question up front:  How to Choose all records from TableA that match TableB, while eliminating from the resultSet all matching records from Table C.

Complicated (real) version:   The goal is to display Pictures for all Listings except the selected Business.

I have 4 Tables.  Listings, Businesses, Stations,  and Pictures.  All Stations are Businesses, and all Businesses are Listings.  Some Listings have pictures.  The following queries (Numbered for clarity) 1) pull all data from the Stations table for the Station the user has chosen, 2) Choose the top Ten results from the Listings table that are within a distance from the selected station, and display the pictures for it.  This works great.

Now I'm attempting to remove from the result set every result that is the same Business type as the selected Station, my attempt shown in 3).  It works, but it repeats some of the results. (The Listings table, Pictures table and Businesses Table all have ListingID).   I cannot cull the final resultSet from 2) simply because I HAVE to have 10 results, so the limiting factor (of not showing any businesses that matches the prodType) has to be applied within the query.

I've tried SELECT DISTINCT and 23235 other things to no avail, and basically think a subQuery within might do the trick, but keep getting zapped on syntax.  Perhaps a more elegant solution comes to mind?  I'm hitting my head HARD against the wall for the last three days.  Tomorrow morning is D-Day.  HELP!  Thanks!

1) <cfquery datasource="DB" name="rsStation">
      SELECT l.*, b.prodType
      FROM Listings AS l
            JOIN Stations AS s ON l.ListingID = s.ListingID
            LEFT OUTER JOIN Businesses AS b ON l.ListingID = b.ListingID
      WHERE s.StationID = <cfqueryparam value="#Form.StationID#" cfsqltype="cf_sql_bigint">
</cfquery>

2) <cfquery name="rsPics" datasource="#application.constant.dsn#">
              SELECT TOP 10
                        l.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height,
                        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>

3) <cfquery name="rsCoupons" datasource="#application.constant.dsn#">
              SELECT TOP 10
                        l.*, b.*, p.PictType, p.PictID, p.Extension, p.Width, p.Height,
                        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
                          JOIN Businesses AS b ON l.ListingID = b.ListingID
                        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 AND b.prodType != <cfqueryparam value="#rsStation.ProdType#" cfsqltype="cf_sql_bigint">
              ORDER BY Distance
</cfquery>
0
Upekrithen
Asked:
Upekrithen
  • 5
1 Solution
 
cmhuntyCommented:
Answer to simple question.......

SELECT * FROM
(
   SELECT a.id FROM tableA AS a INNER JOIN tableB AS b ON a.id = b.id
) AS d
LEFT OUTER JOIN tableC AS c ON c.id = d.id
WHERE d.ID is NULL

Sorry, I don't have time at the moment to go through the complicated question but this may help for a start.
0
 
UpekrithenAuthor Commented:
Ahh...  I've tried 40 billion (right at) configurations of the simple answer, but it doesn't work on the complicated real one.  
0
 
UpekrithenAuthor Commented:
Still trying to fix this, no comments that address the issue, so I'm closing this out.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
UpekrithenAuthor Commented:
Please cancel this question and refund the points.
0
 
UpekrithenAuthor Commented:
Well, it has been almost a year since this was open, so I'm PRETTY sure that no moderators are going to close the question.  I guess I'll give the points to the one person who at least gave ANY answer, though it didn't do the trick.
0
 
UpekrithenAuthor Commented:
Congratulations!  You win the points, and I appreciate the effort.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now