Hi! I have a one-to-many query problem that crops up for me when I try to return unique values that may have multiple records on a joined table.

I have a db with 3 tables.

INVOICE
    sys_record_num (unique on this table)
inv_num
    inv_amt
    inv_pay_date
    po_num

PART_DETAIL
    sys_record_num (may recur on this table)
    part_num
    part_desc

PURCH_ORDER
    po_num
    po_type

My query looks like:

SELECT
t1.sys_record_num,
t1.inv_num,
t1.po_num,
t2.part_num,
t2.part_desc,
t3.po_type

FROM
invoice t1,
part_detail t2,
purch_order t3

WHERE
t1.sys_record_num = t2.sys_record_num and
t1.po_num = t3.po_num and
t3.po_type = ‘material’


In this one (t1) to many (t2) query, I’m trying to pull a data set in which each row is a record of the invoice with part information, and where there are mutliple parts in t2 for a given sys_record_num in t1, there will be multiple lines in the data set.

Instead, when I execute this query I get a data set with one sys_record_num value and one inv_num value, both repeating down the entire set, and part_num and part_desc values changing row to row. For example:

sys_record_num inv_num part_num part_desc po_type
1235 55657 Ak112 repositor material
1235 55657 991kk1 magmon material
1235 55657 boiloff material
1235 55657 11AA2 tooling material
1235 55657 99af991 assy kit material
1235 55657 MRIdrive7 material…etc

In my DB, there is only one part record on t2 where sys_record_num = 1235 and inv_num = 55657. I'm just not sure why the query is repeating the sys_record_num and inv_num values for each row. Its should be moving to the next record.

I know I just need a different kind of join relationship here, but have no idea how to express it properly.

Thank you in advance for any help you can offer.[/size=6]

what's wrong with it? you have tried to give us as much detail as possible but I want some sample data and output result you are expecting. give us two three records for each table and output you are looking for.

i dont think so po_num should be in other table (t3).
is po_num unique in t1? if yes you can have the purchase type
in t1 itself .
i think ponum should be unique for every sysrecord


thr

Thank you very much for your responses.

As to the location of po_num: It can't be moved to a different table in the schema.

To explain the the expected output a little better: sys_record_num is the "primary key" between t1 and t2, So, I'm expecting that the data set should have unique values for sys_record_num, expect where there is more than one entry on t2 for sys_record_num, in which case I'd expect as many rows as there are rows on t2 for that sys_record_num. What I'm getting in reality is only one sys_record_num and all the different values from t2. It's as if all the parts on t2 were associated with just one sys_record_num, which is not the case. Currently, in t1 there are 10,000+ records with only one corresponding record in t2. For these, I'd expect my query show a row of data containing that sys_record_num as a unique value found no-where else in the data set:

sys_record_num inv_num part_num part_desc po_type
12345 6789 XRay11AA tube material

And where there is more than one part on t1 for a given value of sys_record_num I'd expect more than one row, and sys_record_num would be repeated for each part record on t2 with that sys_record_num. In the following example, let's say that three parts appear on t2 for sys_record_num <12345>, the records in the data set would look like:

sys_record_num inv_num part_num part_desc po_type
12345 6789 XRay11AA tube material
12345 6789 Perixray1 slot material
12345 6789 UltrsndP1 probe material

The data set would then show the next unique sys_record_num appearing in t1 and it's associates part records from t2, if not null.