Finding Missing records between two tables!!!

[dpk136]

I have 2 tables, one is called arlispend and the other spendforecastlink. these two tables are linked together on the fields PRONUM and LINITENUM (same in both tables). in table spendforecastlink i get linitenum for line item 1,2,3,4, and 5. in arlispend i only get linitenum for line items 2,4, and 5. I want to find the missing linitenum from the Arlispend table using the existing ones in the spendforecastlink table.

i used this query

Select distinct spendforecastlink.fcLIID,spendforecastlink.pronum,spendforecastlink.LINITENUM
from spendforecastlink left join ArLiSpend
  on ArLiSpend.PRONUM=spendforecastlink.pronum and ArLiSpend.LINITENUM=spendforecastlink.linitenum
where spendforecastlink.pronum='05AR1974' and ArLiSpend.GMTImportID=135 and ArLiSpend.LINITENUM is null

and i got no results. i know that this exists and should have 1 and 3 showing up as having no records that match. can anyone please help...this is very urgent.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

The thing you need to use is called an OUTER JOIN. (Sometimes called LEFT OUTER JOIN.) The syntax varies according to the specific DB. Check your manuals.

Or, post again with more specific details about the DBMS you are using.

 

[dpk136]

It is SQL Server. I tried the left outer join and got nothing

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

Since your problem as stated suggests that you are not getting any records from ArLiSpend, it is illogical to include ArLiSpend.GMTImportID=135 in the WHERE clause, since there won't be any value in that column.

Try something like this (I haven't tested it. But if you can't sort it out, let me know and I can build a small test environment here to make sure it works. But I would need some sample data from you before I could do that.)
[tt]
Select s.fcLIID, s.pronum, s.LINITENUM
from spendforecastlink s
left outer join ArLiSpend a on s.PRONUM=a.pronum
and s.LINITENUM=a.linitenum
where s.pronum='05AR1974'
and a.GMTImportID is null
[/tt]
BTW, note that SQL Server doesn't care what kind of "white space" you use in expressing your SQL. Careful indentation and alignment makes it much easier to read. Using a short-cut name surrogate for the table names helps too.

 

[dpk136]

i used

Select s.fcLIID, s.pronum, s.LINITENUM
from spendforecastlink s
left outer join ArLiSpend a on s.PRONUM=a.pronum
and s.LINITENUM=a.linitenum
where s.pronum='05AR1974'
and a.LINITENUM is null

because the importid is just the number of the import that needs to be pulled in.

if i take out the last line "a.linitenum is null" i get a lot of records.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

So, does that mean you have the results you are looking for?

If not...
When I look at your original post, it sounds like you already know that linitenum values 1 and 3 are missing from the arlispend table WHERE pronum='05AR1974' -- so what is it that you are hoping to find?

 

[dpk136]

a simplified version of the data looks like this.

spendforecastlink
fcLIID     PRONUM     LINITENUM
9886       05AR1974   0001
9940       05AR1974   0002
10001      05AR1974   0003
10072      05AR1974   0004
10267      05AR1974   0005

ArLiSpend
ARSpendID  PRONUM     LINITENUM
5105685    05AR1974   0002
5105686    05AR1974   0002
5105687    05AR1974   0004
5105688    05AR1974   0004
5105689    05AR1974   0005
5105690    05AR1974   0005

I want it to come up with results:

fcLIID     PRONUM     LINITENUM
9886       05AR1974   0001
10001      05AR1974   0003

Then i can insert these into the ArLiSpend table to make complete

I hope this clears up any confusion.

thanks again

David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

well, this is where we found the problem. i need this to happen on a much larger scale for all of the records, not just this one PRONUM

David Kuhn
------------------

http://www.davidpkuhn.com

 

[JarlH]

What about this:

select * from t1
where not exists (select * from t2
where t1.c1 = t2.c1
and t1.c1 = t2.c2);

Just replace column and table names...

 

[dpk136]

nope...still doesn't work.

this is what i ran
select * from spendforecastlink s
where not exists (select * from arlispend a
where s.pronum = a.pronum
and s.linitenum = a.linitenum);

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

Ok, so here is a script that does that. It includes my original SQL with one modification (you didn't provide data for GMTImportID)

CREATE TABLE  #spendforecastlink
   (fcLIID INT, PRONUM CHAR(8), LINITENUM INT)
INSERT INTO #spendforecastlink VALUES(9886,  '05AR1974', 0001)
INSERT INTO #spendforecastlink VALUES(9940,  '05AR1974', 0002)
INSERT INTO #spendforecastlink VALUES(10001, '05AR1974', 0003)
INSERT INTO #spendforecastlink VALUES(10072, '05AR1974', 0004)
INSERT INTO #spendforecastlink VALUES(10267, '05AR1974', 0005)

CREATE TABLE #ArLiSpend
   (ARSpendID INT,  PRONUM CHAR(8), LINITENUM INT)
INSERT INTO #ArLiSpend VALUES(5105685, '05AR1974', 0002)
INSERT INTO #ArLiSpend VALUES(5105686, '05AR1974', 0002)
INSERT INTO #ArLiSpend VALUES(5105687, '05AR1974', 0004)
INSERT INTO #ArLiSpend VALUES(5105688, '05AR1974', 0004)
INSERT INTO #ArLiSpend VALUES(5105689, '05AR1974', 0005)
INSERT INTO #ArLiSpend VALUES(5105690, '05AR1974', 0005)

Select s.fcLIID, s.pronum, s.LINITENUM
 from #spendforecastlink s
   left join #ArLiSpend a on s.PRONUM=a.pronum
                         and s.LINITENUM=a.linitenum
  where s.pronum='05AR1974'
    and a.ARSpendID is null

DROP TABLE #spendforecastlink
DROP TABLE #ArLiSpend

Notes:
1. SQL Server table names starting with # are temporary tables and do not mess with the system catalog.
2. LEFT JOIN or LEFT OUTER JOIN - it doesn't matter. I prefer to include the "OUTER" just to make it clearer.

 

[dpk136]

that runs. but when i take out the select statement and use just that. it shows me nothing

here is the code i'm using now.
Select s.fcLIID, s.pronum, s.LINITENUM
from spendforecastlink s
left join ArLiSpend a on s.PRONUM=a.pronum
and s.LINITENUM=a.linitenum
where s.pronum='05AR1974'
and a.ARSpendID is null

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

Then there is something about the data that is not clear. The test script produces exactly what you said you wanted:
[tt]
fcLIID pronum LINITENUM
----------- -------- -----------
9886 05AR1974 1
10001 05AR1974 3
[/tt]
Your sample data must not be representative of what is actually in the database. Could it be confusion between zero-5-A-R and Oh-5-A-R?

I'm grasping at straws here.

 

[dpk136]

No. Could it possibly any indexes or constraints. I found a work around for now. I am just creating temp tables using the original tables' data. Then i look at that data.



David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

This is my new procedure that works.

CREATE TABLE  #spendforecastlink
   (PRONUM varCHAR(24) Not NULL,LINITENUM varchar(5) Not NULL,fcLIID INT Not Null,modTimestamp datetime Not Null,moduserid varchar(50) NULL)
INSERT INTO #spendforecastlink select * from SpendForecastLink

CREATE TABLE #ArLiSpend
   (ARSpendID INT NOT NULL,GMTImportID INT NULL,PRONUM varCHAR(24) NOT NULL,LINITENUM varchar(5) NULL,SPEYEA varchar(4) NULL,LINITETYP varchar(4) NULL,LINITEPLA money null,WORORDEST money null,COMTOT money NULL,SPETOT money NULL,LINITESELID varchar(8) NULL,LINITEDESG varchar(4) NULL,LINITEDESC varchar(70) NULL,LiStatus varchar(1) NULL)
INSERT INTO #ArLiSpend Select * from ArLiSpend where gmtimportid=@GMTImportID

Select s.fcLIID, s.pronum, s.LINITENUM
 from #spendforecastlink s
   left join #ArLiSpend a on s.PRONUM=a.pronum
                         and s.LINITENUM=a.linitenum
  where a.ARSpendID is null

DROP TABLE #spendforecastlink
DROP TABLE #ArLiSpend

David Kuhn
------------------

http://www.davidpkuhn.com

 

[Zathras]

Ok. I'm glad you have something that works.

One parting word of advice. Unless you are absolutely sure that the table structures will never change, that script will break as soon as a new column is added to either of your two main tables. It would be better to set it up for the specific columns you need:
[tt]
CREATE TABLE #spendforecastlink
(
PRONUM varCHAR(24) NOT NULL,
LINITENUM varchar(5) NOT NULL,
fcLIID INT NOT NULL
)
INSERT INTO #spendforecastlink
select PRONUM,LINITENUM,fcLIID
from SpendForecastLink

CREATE TABLE #ArLiSpend
(
ARSpendID INT NOT NULL,
PRONUM varCHAR(24) NOT NULL,
LINITENUM varchar(5) NULL
)
INSERT INTO #ArLiSpend
Select ARSpendID,PRONUM,LINITENUM
from ArLiSpend
where gmtimportid=@GMTImportID

Select s.fcLIID, s.pronum, s.LINITENUM
from #spendforecastlink s
left join #ArLiSpend a on s.PRONUM=a.pronum
and s.LINITENUM=a.linitenum
where a.ARSpendID is null

DROP TABLE #spendforecastlink
DROP TABLE #ArLiSpend
[/tt]

 

[dnoeth]

There's no need for temporary tables, you probably just need to move the search condition on the inner table to the ON-clause:

Select distinct
spendforecastlink.fcLIID,
spendforecastlink.pronum,
spendforecastlink.LINITENUM
from spendforecastlink left join ArLiSpend
on ArLiSpend.PRONUM=spendforecastlink.pronum
and ArLiSpend.LINITENUM=spendforecastlink.linitenum
and ArLiSpend.GMTImportID=135
where spendforecastlink.pronum='05AR1974'
and ArLiSpend.LINITENUM is null

And the not exists version should work, too:
select * from spendforecastlink s
where spendforecastlink.pronum='05AR1974'
and not exists
(select * from arlispend a
where s.pronum = a.pronum
and s.linitenum = a.linitenum
and ArLiSpend.GMTImportID=135 );

Dieter