Wierd SQL...

[NSMan]

I'm trying to so something with SQL that I can't quite get a handle on. I have a program that is supposed to be able to handle batch scanning. When it scans multiple pages, it links them all to one record that may be defined in scripting code, but it doesn't support linking each individual image to a different record.

When I scan three pages in, basicly I get nine different records.

imglinks
---------------------------------
ilinkid|imgid|userdataid 
51      60    998 
52      61    998
53      62    998

userdata
---------------------------------
id
998
999
1000

imgtable
---------------------------------
imgid|img
60    xyakkdnal;diaand,a
61    iw:kkiefasdffasdna
62    wafwefffffaadfbaaa

I need my userdata id in imglinks to be 998, 999, and 1000. I can define a group of imglinks records that I want linked to a group of userdata records, but there is nothing logical (that I can find) to link them together with. Is there any way I can do this?

 

[hneal98]

What does your SQL look like?

 

[NSMan]

I believe I just got it. It isn't pretty, but basicly I just do a count on both the imglinks table, and the userdata, (object) table with a criteria that filters out my newly scanned images, and then links the ilinks table to the object table using the running count of my ilinks records, and the running count of my object records.

Sorry about the formating, I had it formatted right in QA, but this entry screen doesn't seem to like formating

 update imglinks set 
  imglinks.objidref=object2.id 
 from 
  (
   select
    objidref,
    ilinkid,
    (select 
       count(objidref) 
      from imglinks img2 where
       img2.ilinkid<=imglinks.ilinkid and
       imglinks.objidref in (select id from object where typ=2070 and link1=539493581 and a4='inserting') and 
       img2.objidref in (select id from object where typ=2070 and link1=539493581 and a4='inserting')) as count 
   from imglinks where 
    objidref in (select id from object where typ=2070 and link1=539493581 and a4='inserting')) as imglinks2,
  
  (
   select
    id,
    (select count(id) from object obj2 where obj2.id<=object.id and typ=2070 and link1=539493581 and a4='inserting') as count
   from object where 
    typ=2070 and 
    link1=539493581 and
    a4='inserting'
  ) as object2
 where object2.count=imglinks2.count and imglinks.ilinkid=imglinks2.ilinkid

The two derived tables look like this

object2
-----------------
id|count
33 1
34 2
35 3

imglinks
-----------------
ilinkid|objidref|count
23      33       1
24      34       2
25      35       3

 

[NSMan]

correction, the two tables look like this...

object2
-----------------
id|count
33 1
34 2
35 3

imglinks
-----------------
ilinkid|objidref|count
23      33       1
24      33       2
25      33       3

and I'm able to update my objidref field based on the id in the object2 derived table with the same count.