1. Rick Domogalik
  2. PowerBuilder
  3. Wednesday, 20 November 2019 16:01 PM UTC

I have 3 tables.

 

Table 1

DOC_ID

Table 2

DOC_ID
KEYWORD

Table 3

VIEW_ID
KEYWORD

I am using 3 tables to retrieve a view of data based on what the user defines.  Kind of like a speed-dial or favorite.  The user can define what "keywords" will determine what will appear in the view they define.  The first table is a list of documents in the system.  These documents can have 0, 1 or many keywords added to them.  These keywords are stored in Table 2.  The 3rd table is a list of keywords for the view the user has defined.  I have a treeview that when the user clicks on the view it retrieves a doc list on the right based on the keywords defined for the view.

The problem I am having is how to JOIN Table 1 to 2.  When I join them, it duplicates the rows from table 1 for each keyword added to table 2.  

I feel like I am missing something simple, but have been staring at this for days.

I tried building a temp table and testing each doc to see if it meets the view criteria, but there are thousands of documents and the function was incredibly slow and will not be acceptable to users.   I have also looked at "Exists".  This seems to be the way to go to do a quick retrieval, but I still get duplicates.  

Hoping someone has done a retrieval like this and can point me in the right direction.

Thanks.

 

Rick 

 

 

Accepted Answer
Ferdinand Fischer Accepted Answer Pending Moderation
  1. Wednesday, 20 November 2019 16:42 PM UTC
  2. PowerBuilder
  3. # Permalink

HI,

if i understand you question correctly, you want to retrieve all documents that have the clicked keyword.

 

i would do it like this

SELECT DOC_ID.ID from

DOC_ID

where exists(SELECT 1 from  DOC_ID_KEYWORD

where DOC_ID_KEYWORD.DOC_ID = DOC_ID.ID  and DOC_ID_KEYWORD.KEYWORD = CLICKEDKEYOWRD).

 

Regards

Comment
  1. Rick Domogalik
  2. Thursday, 21 November 2019 18:57 PM UTC
Worked like a charm. I was missing the second qualifier in the where clause. Once I put that in, it resolved the dup issue. Thanks!
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 20 November 2019 16:17 PM UTC
  2. PowerBuilder
  3. # 1

I'm afraid I don't understand much. Can you upload the CREATE TABLE statements of these 3 tables, + your view and select statements, maybe we'll understand it better?

regards.

Comment
  1. Miguel Leeuwe
  2. Wednesday, 20 November 2019 16:18 PM UTC
Consider using "Distinct".
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Wednesday, 20 November 2019 16:39 PM UTC
  2. PowerBuilder
  3. # 2

what is it you are trying to do?  there is no problem, you are getting what you are supposed to get. 

you can post your sql and then say what it is you are trying to do.

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.