Same table repeat in from clause

[Former Member]

Hi,

How do we write query where the same table is repeated more than once since that table has to be join with more than one table .Pls advise.

Rgds,

Rajeev

[former_member583013 · Accepted solution]

Rajeev,

When you join the same table more than once use a different Table alais

Example:

[dbo].[ORDR] T0 INNER JOIN ......... [dbo].[ayx]..INNER JOIN [dbo].[ORDR] T2

Using different alias is a way to segregate them

[Former Member · Accepted solution]

Hi suda ,

If we give different alias for same table in from clause in the select clause which alias name for a field of a particular table to be used.

Rgds,

Rajeev

[former_member583013 · Accepted solution]

It will depend on your query. Knowing what you need is important to answer your question correctly.

[Former Member · Accepted solution]

Hi sudha,

Pls see the below query :

when I run the below query it returns me all the goods receipt and the corresponding batch details.

SELECT * FROM OIBT T0 RIGHT JOIN PDN1 T9

ON T9.LineNum = T0.BaseLinNum AND T0.BaseNum = T9.DocEntry AND T0.ItemCode = T9.ItemCode

when I run the below query , it gives me duplication .It shows duplicate batch details for goods receipt items.Here in below example the table PDN1 is repeated twice with alias name T1 and T9

SELECT DISTINCT T1.DocEntry"GR No",T1.ItemCode"Part No.", T1.Dscription"Part Description",T0.SuppSerial"DateCode",T0.Quantity"Qty", T2.Custom,T1.Price, T2.Cost FROM PDN1 T1 LEFT JOIN IPF1 T2 ON T2.BaseEntry = T1.DocEntry AND T1.ItemCode = T2.ItemCode AND T2.Origline = T1.LineNum,OIBT T0 RIGHT JOIN PDN1 T9 ON T9.LineNum = T0.BaseLinNum AND T0.BaseNum = T9.DocEntry AND T0.ItemCode = T9.ItemCode

Pls guide me

Rgds,

Rajeev

[Former Member · Accepted solution]

Hi,

I believe the problem with the above query is with repeating the table with different alias names because when I run the query with only 2 tables like OIBT and PDN1 or pdn1 and ipf1 in a single query it returns the correct values .Pls advise

Rgds,

Rajeev

[Former Member · Accepted solution]

You can try this rewrite query to see if the results are the same:

SELECT DISTINCT T1.DocEntry 'GR No', T1.ItemCode 'Part No.', 
T1.Dscription 'Part Description', T0.SuppSerial 'DateCode', 
T0.Quantity 'Qty', T2.Custom,T1.Price, T2.Cost 
FROM PDN1 T1 
LEFT JOIN IPF1 T2 ON T2.BaseEntry = T1.DocEntry AND 
T1.ItemCode = T2.ItemCode AND T2.Origline = T1.LineNum
LEFT JOIN OIBT T0 ON T1.LineNum = T0.BaseLinNum AND 
T0.BaseNum = T1.DocEntry AND T0.ItemCode = T1.ItemCode

There are not two alias but one. If you still has duplicate records, it means you have to omit the column with difference data if you can.

Thanks,

Gordon

[Former Member · Accepted solution]

thanks gordon!!!!!