SQL Newbie - Is a Self Join what I need here?

[tschallb]

Hi Folks,

Last week you folks were kind enough to assist me with a situation where a Self Join solved my issue. I thought I understand Self Joins and have been attempting to get one to work without success for the following issue.

Return set is:

INV Line# PO Desc Acct ID AMT
1 0 123 DESC (null) 0 31.91
1 1 123 (null) E 11130 31.91
1 0 123 Desc 1 (null) 0 66.52
1 1 123 (null) E 11152 66.52
...

I need to "merge" each set of two items into one item to look like this:
INV Line# PO Desc Acct ID AMT
1 0 123 DESC E 11130 31.91
1 0 123 Desc 1 E 11152 66.52

I tried a number of different self joins but could not get the result set I needed.

Thanks!
Tim

 

[ESquared]

Show us what you've tried!

Imagine two separate tables:

[tt]Table1[/tt]
[tt]INV Line# PO Desc AMT
1 0 123 DESC 31.91
1 0 123 Desc 1 66.52[/tt]

[tt]Table2[/tt]
[tt]INV Line# PO Acct ID AMT
1 1 123 E 11130 31.91
1 1 123 E 11152 66.52[/tt]

How would you join them together to get what you want? Write a query against these two imaginary tables to do so.

Then, since you know the two tables really are the same one, modify the resulting query to use the right tables with the right data.

I could just give you an answer, but...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,

Thanks for the challenge. I don't learn anything if I just get the answer...

I can't seem to get past a result set that looks like:

INV DESC ACCT AMT
1 (null) 1 16.77
1 (null) 2 206.58
1 DESC 0 16.77
2 DESC 1 0 206.58

My last query attempt is as follows:
SELECT DISTINCT ESA.INV_DOC_NO, ESA1.INV_DOC_NO, ESA1.ITEM_DESC,
ESA1.ACCT_ID, ESA1.INV_AMT
FROM dbo.ESAINVCD ESA
FULL OUTER JOIN dbo.ESAINVCD ESA1 ON ESA.INV_DOC_NO=ESA1.INV_DOC_NO
WHERE ESA.INV_DOC_NO = '316893' /*simplify the set*/

From this query I expected to find a line of data in the result set that I then could add a conditional statement(s)to the query to return the dataset I need. But in this case the Acct_ID is never populated when the rest of the fields are populated.

Thanks,
Tim

 

[ESquared]

Why are you doing an outer join? An inner join is called for here. Let me get you started:

[tt]Table1
INV Line# PO Desc AMT
1 0 123 DESC 31.91
1 0 123 Desc 1 66.52

Table2
INV Line# PO Acct ID
1 1 123 E 11130
1 1 123 E 11152[/tt]

SELECT
   T1.PO,
   T1.Desc,
   T1.AMT,
   T2.Acct,
   T2.ID
FROM
   Table1 T1
   INNER JOIN Table2 T2 ON T1.PO = T2.PO

Now, how do you make the real table act like the separate Table1 and Table2?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ESquared]

Oh... and there should be no need for using the DISTINCT keyword. Also, as a hint, you'll need to put some conditions in, which can be in a WHERE clause or in the FROM clause (although I prefer to put them in the join because they are logically about how the tables join, not about selecting individual rows based on their specific data).

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,
The Outer Join was just the last thing I tried... Thanks for the hint.

I altered my query to the following:

SELECT ESA.PO_NUM,
ESA.ITEM_DESC,
ESA.INV_AMT,
ESA1.ACCT_ID
FROM dbo.ESAINVCD ESA
INNER JOIN dbo.ESAINVCD ESA1 ON ESA.PO_NUM=ESA1.PO_NUM
WHERE ESA.INV_DOC_NO = '316893' and
ESA.ITEM_DESC is not NULL and
ESA1.ACCT_ID > '0'

Now my result set is:

INV PO Desc AMT ACCT
1 123 DESC 31.91 11130
1 123 Desc 1 66.52 11130
1 123 Desc 2 16.77 11130
...
Which is really close... Now each Acct # is repeated 16 times for each Desc which is the number of unique descriptions. For a total of 256 rows instead of 16. I have tried an ACCT_ID comparison between the two tables but where they match the Desc is a Null field. I don't immediately see a way to trim the resultset but I will continue to look.

Also, may I ask what made you use the PO field and not the Invoice field? I never tried the PO field until you suggested it and then I received the result set that I was expecting... Which has me doubfounded (which is not hard...)

Thanks,
Tim

 

[ESquared]

Can you have multiple purchase orders per Invoice? I chose PO because it seemed more specific to me, as I believe this is possible.

May I suggest that you use ESA1 and ESA2? It is confusing to see ESA and ESA1.

Tell me if this works:

SELECT
   ESA1.PO_NUM, 
   ESA1.ITEM_DESC, 
   ESA1.INV_AMT, 
   ESA2.ACCT_ID 
FROM
   dbo.ESAINVCD ESA1 
   INNER JOIN dbo.ESAINVCD ESA2
      ON ESA1.PO_NUM=ESA2.PO_NUM
      AND ESA1.[Line#]=0
      AND ESA2.[Line#]=1
WHERE
   ESA1.INV_DOC_NO = '316893'

(There's nothing that says I can't put the line# conditions in the where clause, but I prefer putting them in the from clause, because then you have less rows being joined, as well as it being more clear what is your selection criteria and what is just making the virtual tables.)

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,

Thanks for the comments. Yes, I can have multiple POs per invoice. Your last snippet of Code returns the same 16 Account Numbers for each Description. Here is the code with the return set:

SELECT
ESA1.PO_NUM,
ESA1.ITEM_DESC,
ESA1.INV_AMT,
ESA1.LINE_NO,
ESA2.ACCT_ID,
ESA2.LINE_NO
FROM
dbo.ESAINVCD ESA1
INNER JOIN dbo.ESAINVCD ESA2
ON ESA1.PO_NUM=ESA2.PO_NUM
AND ESA1.[Line_No]=0
AND ESA2.[Line_No]=1
WHERE
ESA1.INV_DOC_NO = '316893'

returns:
PO DESC INV ESA1.LINE_NO ACCT ESA2.LINE_NO
1 Desc1 16.77 0 11130 1
1 Desc2 45.42 0 11130 1
repeats for each of the 16 Descriptions with the same Acct # then the Descriptions are repeated with the next Acct # and so on.

The returnset with the line conditionals commented out is:
PO DESC INV ESA1.LINE ACCT ESA2.LINE
1 Desc1 16.77 0 0 0
1 (null) 16.77 1 0 0
1 Desc2 38.40 0 0 0
1 (null) 38.40 1 0 0
...
sequence repeats for the sixteen descriptions and then adds the acct# and repeats
PO DESC INV ESA1.LINE ACCT ESA2.LINE
1 Desc1 16.77 0 11130 1
1 (null) 16.77 1 11130 1
1 Desc2 38.40 0 11130 1
1 (null) 38.40 1 11130 1
...

Thanks a bunch for your help,
Tim

 

[ESquared]

I need more information on what the original data looks like. I know what result you want, but I don't know what I'm dealing with.

Thanks!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,

Thanks once again for all of your help. I was able to get the query to work! (I might be figuring out this Self Join stuff yet...). After your last suggestion I scoured the table in question looking for one more unique field that I can base my query on (the table has approx 50 fields). I came across a PO Line_ID field that I gives me the additional info we need. I altered the query to be:

SELECT
ESA1.PO_NUM,
ESA1.PO_LINE_ID,
ESA1.ITEM_DESC,
ESA1.INV_AMT,
ESA1.LINE_NO,
ESA2.ACCT_ID,
ESA2.PO_LINE_ID,
ESA2.LINE_NO
FROM
dbo.ESAINVCD ESA1
INNER JOIN dbo.ESAINVCD ESA2
ON ESA1.PO_NUM=ESA2.PO_NUM
AND ESA1.[Line_No]=0
AND ESA2.[Line_No]=1
and ESA1.PO_LINE_ID = ESA2.PO_LINE_ID
WHERE
ESA1.INV_DOC_NO = '316893'

Which gives me the return set:

PO PO_LINE_ID DESC INV LINE ACCT PO_LINE_ID LINE
1 1 Desc 31.91 0 11130 1 1
1 2 Desc2 66.52 0 11152 2 1
and so on for the 16 entries I was after.

I think this will do it. This particular Invoice was by far my worst case scenario.

Thanks once again,
Tim

 

[ESquared]

Great! I didn't know enough about the structure of your data, obviously.

I'm glad you were able to figure it out largely on your own... this means you'll be more equipped to do the job next time, too.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Oh, so close. In testing occassionally the right record that I need to return has a ESA2.LINE_NO of 2 or higher. Which led me to the notion that I simply need to alter the command to a MAX aggregate function.

I have tried the following:

AND ESA2.[Line_No]=(select max(ESA2.SUB_LINE_NO) from ESA2)
&
AND ESA2.[Line_No]=(select max(ESA2.SUB_LINE_NO))

Which of course gives me an error. I also tried moving the Conditionals from the Join to the Where clause with no luck.
I searched the Archives and have not found anything to give me a clue.
Any suggestions?
Thanks,
Tim

 

[ESquared]

I still don't know what your data actually looks like.

Give me two samples, one normal and one with the line_no higher. Explain how to determine the correct lines to use in each case.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,

Here you go. Here are real snippets from the table.

(Column names shortened to get info to fit...)

First: Normal case "Two lines of information"

INV, LINE_NO, PO_LINE, PO#, DESC, ACCT_ID, AMT
317, 0, 1, 78, Tree Pruning, 0, 39000
317, 1, 1, 78, <NULL>, 6357, 39000

Second: Abnormal case "Three or more lines of information"
INV, LINE_NO, PO_LINE, PO#, DESC, ACCT_ID, AMT
310, 0, 1, 79, Walk of Hon~, 0, 3920
310, 1, 1, 79, or memorial , 0, <NULL>
310, 2, 1, 79, <NULL>, 10608, 3920

The returnset I would like to get is:
INV PO# Desc ACCT_ID AMT
317 78 Tree Pruning 6357 39000
310 79 Walk of~ 10608 3920


Thanks,
Tim


Thanks!

 

[ESquared]

So, instead of using LINE_NO = 1, use ACCT_ID > 0!

E²

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquared,

Thanks for the suggestion. Sometimes I overlook the obvious... That suggestion along with a couple of other tweaks have my query working great. And more importantly I believe I understand what is occurring.

Thanks,
Tim