Trouble shooting SQL

I am experiencing a problem I have no idea how to troubleshoot. The SQL I'm using is:-

SELECT tk.idKeyword, k.keyword, t.name, p.name as path, v.netname, t.thumbnail, t.idThumb
FROM ((((ThumbnailKeyword tk
 INNER JOIN Keyword k ON tk.idKeyword = k.idKeyword) INNER JOIN Thumbnail t ON tk.idThumb = t.idThumb)
  INNER JOIN Path p ON t.idpath = p.idpath)
   INNER JOIN Volume v ON p.idvol = v.idVol)
WHERE k.idKeyword = 151 OR k.idKeyword = 146
ORDER BY t.idPath, t.name

Open in new window


This only returns results for k.idKeyword = 146

However using:-

WHERE k.idKeyword = 151

Open in new window


... returns results. As does

WHERE k.idKeyword = 151 OR k.idKeyword = 147

Open in new window


... or:-

WHERE k.idKeyword = 150 OR k.idKeyword = 146

Open in new window


In fact as far as I can tell any other combination of:-

k.idKeyword = ?? OR k.idKeyword = ??

Open in new window


... works as expected. I'm at a complete loss as to what to look for to troubleshoot this. I would appreciate help on this.
LVL 1
cescentmanAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
cescentman,

I wonder if you could upload your DB to trouble shoot it. Your code seems okay. If I had access to t, I will check on the data type and quality in joining fields and criteria fields.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
What is the problem. You have said what works. But you have not told what doesn't.

Do you mean:

WHERE k.idKeyword = 151 OR k.idKeyword = 146

returns records for 146 only where as you are expecting some records for 151 as well?
0
 
cescentmanAuthor Commented:
Yes sorry If I wasn't explicit enough.

As I say:-

WHERE k.idKeyword = 151

Open in new window


... returns results
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Wilder1626Commented:
Is this working?

SELECT tk.idKeyword, k.keyword, t.name, p.name as path, v.netname, t.thumbnail, t.idThumb
FROM ((((ThumbnailKeyword tk
 INNER JOIN Keyword k ON tk.idKeyword = k.idKeyword) INNER JOIN Thumbnail t ON tk.idThumb = t.idThumb)
  INNER JOIN Path p ON t.idpath = p.idpath)
   INNER JOIN Volume v ON p.idvol = v.idVol)
WHERE k.idKeyword in ('151', '146')
ORDER BY t.idPath, t.name

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Wilder1626,

Do you mean like:

WHERE k.idKeyword in (151, 146)
0
 
Wilder1626Connect With a Mentor Commented:
sorry, yes

SELECT tk.idkeyword, 
       k.keyword, 
       t.NAME, 
       p.NAME AS path, 
       v.netname, 
       t.thumbnail, 
       t.idthumb 
FROM   ((((thumbnailkeyword tk 
           INNER JOIN keyword k 
                   ON tk.idkeyword = k.idkeyword) 
          INNER JOIN thumbnail t 
                  ON tk.idthumb = t.idthumb) 
         INNER JOIN path p 
                 ON t.idpath = p.idpath) 
        INNER JOIN volume v 
                ON p.idvol = v.idvol) 
WHERE  k.idkeyword IN (151, 146 ) 
ORDER  BY t.idpath, 
          t.NAME   

Open in new window

0
 
cescentmanAuthor Commented:
I think there must have been a problem with the original DB. I went back to the parent app that creates the DB and refreshed the data and now all is OK. Many thanks for your help on this.
0
 
cescentmanAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for cescentman's comment #a40543856
Assisted answer: 250 points for Wilder1626's comment #a40543351
Assisted answer: 250 points for eghtebas's comment #a40543372

for the following reason:

Although the matter was resolved by me I thin the points ought to be allocated.
0
 
cescentmanAuthor Commented:
Oops wrong boxes
0
 
Wilder1626Commented:
I'm glad i was able to help
0
All Courses

From novice to tech pro — start learning today.