select statement question

[alank01]

basic problem is as follows
i have three tables, table C has the primary key and unique
entries, A and B hold foriegn keys that are many to one into
table C

table A table B table C
f_key f_key p_key

sample data
data A data B data C
11 12 11
11 12 12
11 14 13
11 15 14
12 15
13
14

i would like a cursor result to look like this

f_key_a f_key_b (don't care about C fields)
11 null
11 null
11 null
11 null
12 12
null 12
13 null
14 14
null 15

anyone suggest an sql statment / view for above? i'm creating the cusor manually at the momement. Note i can't
afford repeating entries for every match between f_key_a and f_key_b. thks

 

[jhall156]

Is this maybe what your're looking for...

SELECT a.field, MAX(b.field )
FROM tablea a LEFT JOIN table b ON a.field == b.field
GROUP BY a.field
ORDER BY a.field JHall

 

[alank01]

thks for effort JHall unfortunately using this select per you suggestion :

Select tablea.fkey,Max(tableb.fkey)from tablea left join tableb ON tablea.fkey == tableb.fkey group by tablea.fkey order by tablea.fkey

result

11 null
12 12
13 null
14 14

i should clarify that although i used numbers for data they are just keys and not values . theres nothing really to calculate. from this example also i loose key value 15 from tableb and repeating key value 11 from tablea etc

 

[jhall156]

Sorry, sounds like what you want is a CROSS JOIN or FULL OUTER JOIN which will produce a cartesian product (i.e. matching every value on the left with every value on the right). Also in your first post you said you couldn't afford repeating entries for every match, but your reply seems to indicate that you do want them. JHall

 

[alank01]

thks JHall. i was not too clear on the repeating values. i need repeating values but not the multiplier effect. in my example i have the key value 12 in both tables i would like to see

12 12
null 12

not the multiplier effect of many selects

12 12
12 12