SQL query - needs UNION?

[dps]

MY query

select DISTINCT p.prd_code, p.prd_name, s.brk_date, s.len_code
from
product p, spot s
where p.prd_code = s.prd_code
and s.len_code in (10,30)


results

PRD_CODE PRD_NAME BRK_DATE LEN_CODE
AA01 AA MEMBERSHIP 23/07/2000 10
AA01 AA MEMBERSHIP 24/07/2000 10
AA01 AA MEMBERSHIP 25/07/2000 10
AA01 AA MEMBERSHIP 26/07/2000 10
AA01 AA MEMBERSHIP 30/07/2000 10
AA01 AA MEMBERSHIP 31/07/2000 10
AA01 AA MEMBERSHIP 02/08/2000 10
AA01 AA MEMBERSHIP 03/08/2000 10
AA01 AA MEMBERSHIP 04/08/2000 10
AA01 AA MEMBERSHIP 05/08/2000 10
AA01 AA MEMBERSHIP 06/08/2000 10
AA01 AA MEMBERSHIP 07/08/2000 10
AA01 AA MEMBERSHIP 08/08/2000 10
AA01 AA MEMBERSHIP 09/08/2000 10
AA01 AA MEMBERSHIP 10/08/2000 10
AA01 AA MEMBERSHIP 11/08/2000 10
AA01 AA MEMBERSHIP 12/08/2000 10
AA01 AA MEMBERSHIP 13/08/2000 10


My question How can I return data where brk_date is common in both tables and so id len_code?? Using a UNION Intersect??

Regards

 

[Turkbear]

Hi,
will not this work?:

select DISTINCT p.prd_code, p.prd_name, s.brk_date, s.len_code
from
product p, spot s
where p.prd_code = s.prd_code
and s.len_code in (10,30)and
s.len_code = p.len_code and
s.brk_date = p.brk_date;

Or am I missing some requirement?

 

[dps]

Sorry my fault.

The product table does not have p.len_code and
p.brk_date

 

[dps]

Sorted!

Requirements have changed(data will be taken from tables where there are matching columns)

 

[carp]

If the product table does not contain a brk_date column, how could you possibly find "where brk_date is common to both tables"?