QUERY Joining tables

[Former Member]

I am working on a query that is excluding records when I add a field from the second table. Before I add T1.[TrgetEntry] I have all Quote records. After I add I lose several hundred records.

I have tried Inner, Left and Right Joins to no avail.

SELECT DISTINCT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocTotal], T0.[DocStatus], T0.[DocDate], T0.[U_PriRepNum], T0.[U_PriRepName], T0.[U_RegName], T0.[U_Enteredby], T0.[U_Order_Type], T1.[TrgetEntry] FROM OQUT T0 Left outer JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

Any suggestions?

Thanks

[former_member541807 · Accepted solution]

hi Steve,

this one should work!

SELECT		T0.DocNum,
			T0.CardCode,
			T0.CardName, 
			T0.DocStatus, 
			T0.DocTotal, 
			T0.DocDate, 
			T1.TrgetEntry,
			T0.U_PriRepNum, 
			T0.U_PriRepName, 
			T0.U_RegName, 
			T0.U_Enteredby, 
			T0.U_Order_Type
FROM        OQUT T0 INNER JOIN
            QUT1 T1 ON T0.DocEntry = T1.DocEntry

using INNER JOIN is good enough to bring all the data you need.

regards,

Fidel

[Former Member · Accepted solution]

Avelino

That works but I had to add the Distinct to limit to one item. I then realized that when a quote is copied to a sales order and all the items on the quote are not used it leave the quote open and the query gives me both results.

1. the quote with the target information

2. the quote same quote because there is still an open item.

To complicate it I can not use the Doc Total because it doubles up. Any ideas to filter that out?

Sorry for the dealy I was on vacation

Appreciate your help

Steve

[Former Member]

Hi Steven

It is true, you use distinct.

Means only show one if all the rows result were same. (because there are some quot rows can target to same docentry)

If you add one more field, T1.LineNum, I am sure it will come out all the result

Hope it can help

Gary

Edited by: garywong0224 on Mar 9, 2012 10:34 AM

[Former Member]

The reason I wanted to use distinct was I only wanted one the data at the header level not the row level. Unfortunately the target document is at the row level.

Thanks for your help and sorry it took so long to repsond as I was on vacation.