What the heck? SQL SELECT statement

[TyB]

I have this SELECT statement that is pulling out data from a DB table. This code returns 5 records.

"SELECT ITEM_DETAILS, PRIORITY, DUEDATE, COMPLETEDON, ISCOMPLETE, LISTID, COMPLETEDBY, TOTALCOST, COMMENTS 
FROM TODO 
WHERE (ISCOMPLETE = @ISCOMPLETE) AND (ISDEPARTMENT ='True') AND (ISUSER ='False') AND (MEMBERS LIKE '%' + @Fullname + '%') 
ORDER BY SUBMITTEDON DESC"

I found that I need to add another qualifier in the WHERE clause from another table so I created this statement.

SELECT TODO.ITEM_DETAILS, TODO.PRIORITY, TODO.DUEDATE, TODO.COMPLETEDON, TODO.ISCOMPLETE, TODO.LISTID, TODO.COMPLETEDBY, TODO.TOTALCOST, TODO.COMMENTS 
FROM TODO, TODO_SUB_ITEMS 
WHERE (TODO.ISCOMPLETE = 'False') AND (TODO.ISDEPARTMENT ='True') AND (TODO.ISUSER ='False') AND (TODO.MEMBERS LIKE '%' + @Fullname + '%') AND (TODO_SUB_ITEMS.HIDE NOT LIKE '%' + @Fullname + '%') 
ORDER BY TODO.SUBMITTEDON DESC

This code returns 895 records. Thats 179 copies of each of the 5 records. It should return 4 records as one should be hidden.

Not sure what is causing the extra records.

Thanks,

Ty

 

[jmcilhinney]

You're performing a join on the two tables but you aren't specifying which columns are to be joined on, so every record in one table is joined with every record in the other table. You obviously only want related records to be joined so you have to specify how they are related. Using your current syntax you would have to add an extra condition to the WHERE clause:

AND TODO.TODO_ID = TO_DO_SUB_ITEMS.TODO_ID

That is outdated syntax though and, assuming your database supports it, you should use proper join syntax:

FROM TODO
INNER JOIN TODO_SUB_ITEMS
ON TODO.TODO_ID = TO_DO_SUB_ITEMS.TODO_ID

You're still going to get duplicates of each item though because you'll get as many records for each TODO item as there are TODO_SUB_ITEM records related to it. To handle that you can add the DISTINCT key word to your SELECT clause, or the equivalent for your database.

Finally, I feel compelled to tell you that it's a scientifically proven fact that the likes of ToDo.ItemDetails is easier to read that TODO.ITEM_DETAILS. Personally, I hate all upper case for identifiers. What you do is up to you but I suggest that, if you can, you change your naming convention.

 

[TyB]

Almost

I found that one of the problems was with the _ in the table name for some reason so I aliased the second table making my code look like....

SELECT TODO.ITEM_DETAILS, TODO.PRIORITY, TODO.DUEDATE, TODO.COMPLETEDON, TODO.ISCOMPLETE, TODO.LISTID, TODO.COMPLETEDBY, TODO.TOTALCOST, TODO.COMMENTS 
FROM TODO, TODO_SUB_ITEMS AS SUB
WHERE (TODO.ISCOMPLETE = 'False') 
AND (TODO.ISDEPARTMENT ='True') 
AND (TODO.ISUSER ='False') 
AND (TODO.MEMBERS LIKE '%' + @Fullname + '%') 
AND (SUB.HIDE NOT LIKE '%' + @Fullname + '%') 
ORDER BY TODO.SUBMITTEDON DESC

So now instead of the 179 copies each of the 5 records I get 3 copies of 1 record instead of the actual expected return results which should be 4 records.

Thanks,
Ty

 

[jmcilhinney]

I don't see either of my suggestions implemented in that code.

 

[TyB]

Same results

jm

This produces the same results which is 3 copies of 1 record instead of the 4 records that fit the statement.

SELECT MAIN.ITEM_DETAILS, MAIN.PRIORITY, MAIN.DUEDATE, MAIN.COMPLETEDON, MAIN.ISCOMPLETE, MAIN.LISTID, MAIN.COMPLETEDBY, MAIN.TOTALCOST, MAIN.COMMENTS 
FROM TODO AS MAIN INNER JOIN TODO_SUB_ITEMS AS SUB ON MAIN.LISTID = SUB.LISTID 
WHERE (MAIN.ISCOMPLETE = 'False') 
AND (MAIN.ISDEPARTMENT ='True')  
AND (MAIN.MEMBERS LIKE '%' + 'BARTON, TYLER' + '%') 
AND (SUB.HIDE NOT LIKE '%' + 'BARTON, TYLER' + '%')  
ORDER BY MAIN.SUBMITTEDON DESC

Ty

 

[jmcilhinney]

It's time to learn a development technique that I would call "back to basics". When something doesn't work the way you expect you don't keep banging your head against a wall. You go back to basics. You create the most basic scenario you can, thus removing as many variables as possible. Assuming that that gives you the expected results, you then take a step closer to your final objective. You keep going like that until you either reach your final objective or you find a step that produces unexpected results. You then analyse exactly what you did wrong at that step, fix it and then continue.

So, you should forget your current query and get back to basics. The first thing is tp perform a simple join:

SELECT Parent.ParentID, Child.ChildID
FROM Parent
    INNER JOIN Child
        ON Parent.ParentID = Child.ParentID

If that gives you what you expect then you might add some more columns to the projection, but only those that are specifically relevant, e.g. ToDo.IsComplete is your case. If you still get what you expect then you can take another step, which would be adding a single filter. Etc., etc.

This is software development. Using logical means to diagnose issues with your code is as big a part of the process as writing the code in the first place.

I'd also question this part of the code:

WHERE (MAIN.ISCOMPLETE = 'False') 
AND (MAIN.ISDEPARTMENT ='True')

That looks like those columns contain text values that represent boolean values. Surely not. If the data represents boolean values then it should contain boolean values. If you're using Access then you should have a Yes/No column and the SQL code should be:

WHERE (MAIN.ISCOMPLETE = False) 
AND (MAIN.ISDEPARTMENT = True)

Note there are no single quotes because the values are not text. They are boolean literals. If you're using SQL Server then you should have a 'bit' column and the SQL code should be:

WHERE (MAIN.ISCOMPLETE = 0) 
AND (MAIN.ISDEPARTMENT = 1)

 

[TyB]

This is resolved

I have redesigned my DB so that I do not need to cross tables for the data which turns out to be more efficient as well.

Thanks for your help.

Ty