2

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
Ron Ronmonstererer Ankrah
  • 71
  • 10
  • MySQL and SQL-Server are not the same thing, which are you actually using? – Barmar Jul 11 '16 at 16:19
  • 2
    You should read about, understand and start using parameterized queries before you expose yourself to sql injection. – Sean Lange Jul 11 '16 at 16:21
  • 4
    your `not in` selects all records from the very table you joined, excluding all records in it. – Marc B Jul 11 '16 at 16:21
  • What are exactly parameterized queries? So by using Not In, I am selecting all records from the table ReportList, by excluding records from it? – Ron Ronmonstererer Ankrah Jul 11 '16 at 16:27
  • You're using a weird `right join` along with `not in`. I can't make sense of what you're trying to accomplish but the problem is that you're doing both at the same time. MarcB already explained the issue. – shawnt00 Jul 11 '16 at 16:34
  • So Prdp, it is passed in the ddl from once a company is selected, the query executes. According to your statement, for each table, alter it and don't check if all of the records are constrained, then launch command to drop the table of constrained records? – Ron Ronmonstererer Ankrah Jul 11 '16 at 16:36
  • 1
    @RonRonmonsterererAnkrah - It will disable all the constraints in your database and drop all the tables in your database. Start using parameterized queries to avoid sql injection like above. – Pரதீப் Jul 11 '16 at 16:40
  • 1
    Prdp was trying to explain what can go wrong with SQL injection attacks. None of that really has anything to do with the question you asked. There's lots of information about SQL injection for you to track down. – shawnt00 Jul 11 '16 at 16:40
  • Am removing my comment that is quite dangerous – Pரதீப் Jul 11 '16 at 16:41
  • OK. Thanks for the clarification Prdp and shawnt00 – Ron Ronmonstererer Ankrah Jul 11 '16 at 16:43

3 Answers3

1

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 = ?
shawnt00
  • 12,316
  • 3
  • 14
  • 19
  • I was able to get the checkboxes to appear and there would be varying number of checked checkboxes, implying those number of checkboxes were appropriate to the company. The problem was the ReportName didn't display or it would be unable to bind. – Ron Ronmonstererer Ankrah Jul 11 '16 at 18:50
0

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
Y. M.
  • 107
  • 9
  • 1
    I did and it worked at first, however, I realized that it would display more values than expected, so my intention was stick to that join statement, show all the reports regardless of company, but exclude the unchecked reports, or null values to be precise. Now, from what you said about zero records is what I am experiencing now. – Ron Ronmonstererer Ankrah Jul 11 '16 at 16:47
  • If you don´t want the null result just use "inner join" than using "right join" ( the right join return null if not found on the other table the left one in this case), look now on the answer with inner join . – Y. M. Jul 11 '16 at 17:05
  • 1
    By using inner join, I got reports appropriate to the company. But now, suppose those appropriate reports were shown, but also other reports not appropriate to the company and were unchecked. Would I revert back to right join or use either NOT IN or NOT EXIST? – Ron Ronmonstererer Ankrah Jul 11 '16 at 18:49
  • What mean Unchecked , is it mean that not on the table CompanyReportListTable, or it mean rl.ReportVisible = 0 – Y. M. Jul 11 '16 at 18:55
  • I believe CompanyReportListTable – Ron Ronmonstererer Ankrah Jul 11 '16 at 19:05
  • See the last example with left join, I thing this is what you are looking for but not sure yet, take it a try. – Y. M. Jul 11 '16 at 19:33
0

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"
Ron Ronmonstererer Ankrah
  • 71
  • 10