I'm sure this one has been answered a million times...
I have 3 tables set up as one to many like:
/T2
T1<
\T3
the one side is on T1 and the many is on both T2 and T3
I have referential integriety set up.
T1 is:
serial num (index)
holdtype
prodcode
custabrev
date
T2 is:
serialnum
loadholdnum
released
T3 is:
serialnum
qualityholdnum
released
Serialnum is where the joins are made.
Here's the lowdown... I generate the loadholdnum and qualityholdnum in sequence and place that number in either T2 or T3 BUT NEVER T2 & T3, so i have a unique hold num in each of the tables. I CAN have more than one hold number assigned to a serial number. ie; 5 loadholdnums and 3 qualityholdnums related to 1 serial number.
Now i'm sure u see the problem here...
i query by serial number and i get:
Lets assume one serial number and the following Lnum and Qnum;
Lnum1=11
Lnum2=22
Qnum1=33
Qnum2=44
my output is:
11 | 33
11 | 44
22 | 33
22 | 44
what i wnat to see is:
11 | 33
22 | 44
or vicevera
11 | 44
22 | 33
I dont much care about the order by i need the result minimized like that..
I hope i got my point across
TIA
Jeff
I have 3 tables set up as one to many like:
/T2
T1<
\T3
the one side is on T1 and the many is on both T2 and T3
I have referential integriety set up.
T1 is:
serial num (index)
holdtype
prodcode
custabrev
date
T2 is:
serialnum
loadholdnum
released
T3 is:
serialnum
qualityholdnum
released
Serialnum is where the joins are made.
Here's the lowdown... I generate the loadholdnum and qualityholdnum in sequence and place that number in either T2 or T3 BUT NEVER T2 & T3, so i have a unique hold num in each of the tables. I CAN have more than one hold number assigned to a serial number. ie; 5 loadholdnums and 3 qualityholdnums related to 1 serial number.
Now i'm sure u see the problem here...
i query by serial number and i get:
Lets assume one serial number and the following Lnum and Qnum;
Lnum1=11
Lnum2=22
Qnum1=33
Qnum2=44
my output is:
11 | 33
11 | 44
22 | 33
22 | 44
what i wnat to see is:
11 | 33
22 | 44
or vicevera
11 | 44
22 | 33
I dont much care about the order by i need the result minimized like that..
I hope i got my point across
TIA
Jeff