CODEWITHSUNDEEP
sql-serverjoinnotin
Ron Ronmonstererer Ankrah
Ron Ronmonstererer Ankrah

Reputation: 71

NOT IN and a SQL join

I've written a SQL statement where these specific columns are from a table and it joins with another table and the primary key, ReportID, serves as the link between the two tables. I am using NOT IN to accurately display the reports of a company, but I get no output when a company is selected. Is there anywhere in the query that I would have to rearrange? 

    valsql1 = "SELECT DISTINCT c.ReportID, c.COMPANYID, rl.REPORTNAME 
                FROM  CompanyReportListTable c 
                right join ReportList rl  on c.reportid = rl.ReportID 
                WHERE c.reportid  NOT IN(Select rl.ReportID FROM ReportList rl) 
                and rl.ReportVisible = 1 
                and CompanyID = " & DropDownList1.SelectedValue

Upvotes: 2

Views: 109

Answers (3)

Ron Ronmonstererer Ankrah
Ron Ronmonstererer Ankrah

Reputation: 71

So here's what I did to solve this problem: remove the DISTINCT keyword, keep the right join for the two tables. Have the reportvisible set to 1. Once a company is selected from the dropdownlist, the reportID under ReportList table, rl, not by using NOT IN, but by using IN, select the ReportID from table 2, ReportList, and keep it visible and order by it's name for clarity. By keeping it in, it will accurately display the results for each company, but this time, un-check the ones un-associated per company. So regardless of the number of reports per company, it will display the ones associated with it. Here's the correct query

valsql1 = "SELECT c.ReportID, c.COMPANYID, rl.REPORTNAME 
FROM  CompanyReportListTable c 
right  join  ReportList rl on c.reportid = rl.ReportID and reportvisible = 1 and CompanyID =" & DropDownList1.SelectedValue & " 
where rl.ReportID IN (Select ReportID from ReportList where ReportVisible = 1) 
order by ReportName"

Upvotes: 0

Y. M.
Y. M.

Reputation: 107

The NOT IN it´s no need for it, why you use in if you just use on the join, there is no benefit of it, the only way just if you have to select other record of the same table (when you use somthing like tree structure), what you are trying to do is all the result will be zero records.

I think you just wanted something like this:

valsql1 = "SELECT DISTINCT c.ReportID, c.COMPANYID, rl.REPORTNAME 
   FROM  CompanyReportListTable c 
         right join ReportList rl  on c.reportid = rl.ReportID 
   WHERE rl.ReportVisible = 1 
         and CompanyID = " & DropDownList1.SelectedValue

And with inner join look like this:

valsql1 = "SELECT DISTINCT c.ReportID, c.COMPANYID, rl.REPORTNAME 
   FROM  CompanyReportListTable c 
         inner join ReportList rl  on c.reportid = rl.ReportID 
   WHERE rl.ReportVisible = 1 
         and CompanyID = " & DropDownList1.SelectedValue

Last example with left join: 

valsql1 = "SELECT DISTINCT c.ReportID, c.COMPANYID, rl.REPORTNAME 
   FROM  CompanyReportListTable c 
         left join ReportList rl  on c.reportid = rl.ReportID 
   WHERE rl.ReportVisible = 1 
         and CompanyID = " & DropDownList1.SelectedValue

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

I don't think you wanted to exclude all the reports. It's really hard to guess at the purpose of the two different tables but I believe you just need to trim the list of reports you're trying to exclude. (In another answer you refer to "unchecked reports, or null values".) 

SELECT ReportID, COMPANYID, REPORTNAME 
FROM CompanyReportListTable c
WHERE
    ReportID NOT IN
        (
        SELECT rl.ReportID FROM ReportList rl
        WHERE ... /* which reports are you trying to exclude? */
        ) 
    AND ReportVisible = 1 AND CompanyID = ?

Upvotes: 1

Related Questions