one to many querys

[jeffmoore]

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

 

[PauloRico]

The only way you can achieve the results you are after is if you place a relation field between T2 and T3. You give an example of 2 records in T2 and 2 records in T3 and show your output as 1 record relating to the other. Yet you also say that there can be 5 records in one table and 3 records in the other. How would you expect the results to be displayed in this situation?

Could you give a bit more information about what these tables hold and how they relate?

 

[Golom]

I agree with PauloRico ... because there is no relationship between the values in the two tables, there are no rules that we can apply to select which records to display or not display. You can get a display however that will show all the LoadHold numbers and all the QualityHold numbers for a Serial Number like this

SELECT L.SerialNum, "Load" As [NumType], L.LoadNum FROM Load As L 

UNION 

SELECT Q.SerialNum,"Quality" As [NumType],  Q.QualNum FROM Quality As Q;

 

[jeffmoore]

Well the only related firld between T2 and T3 is serialnum. I used a union query to build a new table with both T2 and T3's data but then then data gets 'spread out'. ie;
T1 | T2
11 | 0
22 | 0
0 | 33
0 | 44
To answer your Q Paulo
in a 5 by 3 matrix the following would be acceptable:
T1=1,2,3,4,5
T2=6,7,8
T1 | T2
1 | 6
2 | 7
3 | 8
4 | 0
5 | 0
ps 0=null or no data
Any ideas?
Jeff