[RESOLVED] JOIN TABLE to Itself

jeepin81

I'm having trouble executing this SQL statement and getting the data back that I need.

I have a table that has duplicate rows (kind of)... One of the duplicate row has data in 2 columns that I need.

partial example:

Id  Batch_Num  Invoice_Num  SEQ_Num  Acct_Num   Misc  status_code
--------------------------------------------------------------------------------
1  20010001                                         0003          45    2
2  20010001      3456             89877                               10
3  20020002                                         0004          88    2
4  20020002      4667             34566                               10

*** my example table formatting is lost when I submit here is a screen shot:
[ATTACH]4711[/ATTACH]

$sql = "SELECT a.id, a.batch_num, a.seq_num, a.acct_num, b.batch_num, b.invoice_num, b.seq_num
           FROM myfile a, myfile b 
           WHERE
           a.batch_num = b.batch_num
           AND a.status_code = '2' ";

So, I need to get all of the info where the batch_num's are equal and the a.status_code = 2....

I'm not sure what I am missing here? Would I be better off doing a sub_query of some sort?

I appreciate any insight... Thanks!

Screen Shot 2012-10-26 at 2.25.09 PM.png

jeepin81

So, I used aliases for the field names and tables, and used JOIN ON instead of the join with the comma and that appears to work.... Not sure why that would react different... When using a comma as a JOIN is that considered an INNER JOIN?

Here is the query that ran successful:

$sql = "SELECT a.TROUTD AS TROUTDA, 
			   a.STATUS_CODE AS STATUS_CODEA, 
			   a.CLIENT_ID AS CLIENT_IDA, 
			   a.TRANS_AMOUNT AS TRANS_AMOUNTA, 
			   a.BATCH_SEQ_NUM AS BATCH_SEQ_NUMA, 
			   a.INVOICE AS INVOICEA, 
			   a.AUTH_CODE AS AUTH_CODEA, 
			   a.ACCT_NUM AS ACCT_NUMA,
			   a.PAYMENT_MEDIA AS PAYMENT_MEDIAA,
			   b.TROUTD AS TROUTDB, 
			   b.STATUS_CODE AS STATUS_CODEB, 
			   b.CLIENT_ID AS CLIENT_IDB, 
			   b.TRANS_AMOUNT AS TRANS_AMOUNTB, 
			   b.BATCH_SEQ_NUM AS BATCH_SEQ_NUMB, 
			   b.INVOICE AS INVOICEB, 
			   b.AUTH_CODE AS AUTH_CODEB,
			   b.ACCT_NUM AS ACCT_NUMB,
			   b.PAYMENT_MEDIA AS PAYMENT_MEDIAB
			   FROM myschema.mytable AS a JOIN myschema.mytable AS b
			   ON a.TROUTD = b.TROUTD and b.STATUS_CODE = 10
			   WHERE
			   a.CLIENT_ID = 200010001
			   AND a.BATCH_SEQ_NUM = '5'
			   AND a.STATUS_CODE = '2'
			   ";

Weedpacket

jeepin81 wrote:
When using a comma as a JOIN is that considered an INNER JOIN?

INNER JOIN is the default if you don't specify any other type.

There are a lot of NULLs in your sample, which is a little worrying from a design standpoint; is this a table of transactions? In which case, why not have both ends of the one transaction in one row?

jeepin81

This is a table of transactions. We're in the process of switching to a new platform and in preparation we are marrying the live system and the new one in the background in our test schema to ensure everything matches and all of reporting scripts run correctly before the switch.

Thanks for the heads-up on the NULL values, I will address that.