Home » SQL & PL/SQL » SQL & PL/SQL » Inner Join - foreign keys are in a range
Inner Join - foreign keys are in a range [message #252245] Wed, 18 July 2007 05:32 Go to next message
benzene
Messages: 3
Registered: July 2007
Junior Member
Hi -

I am putting together a query which inner joins 2 tables - the primary key (PK) table, and the foreign key (FK) table. This would normally be quite simple, however the FK table actually defines start/end values for the foreign key.

I want to write a query which returns each row from the PK table (no duplicates), where it exists in the range of foreign keys in the FK table.

I guess this could be done with a BETWEEN in the join condition, however I'm not sure it is 'safe' i.e. will not return cartesian products in some situations.

Let me provide the simplest example - an ITEM table, and then a ITEM_RANGE table which defines start/end ITEM PKs.

ITEM (PK table)
-------
ITM_ID

ITEM_RANGE (FK table)
-------------
ITM_ID_START
ITM_ID_END

So if I want to return a result set which is each ITEM whose PK appears in the range in ITEM_RANGE I do this:

SELECT *
FROM item, item_range
WHERE item.itm_id BETWEEN item_range.itm_id_start AND item_range.itm_id_end
/

Will this query return the correct results? I.e will I safely avoid the cartesian product in all cases?

Many thanks,

Ben
Re: Inner Join - foreign keys are in a range [message #252254 is a reply to message #252245] Wed, 18 July 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will this query return the correct results?

Yes.
Quote:
will I safely avoid the cartesian product in all cases?

There is no cartesian product, you have a join condition.

Regards
Michel
Re: Inner Join - foreign keys are in a range [message #252299 is a reply to message #252254] Wed, 18 July 2007 09:24 Go to previous messageGo to next message
benzene
Messages: 3
Registered: July 2007
Junior Member
Thanks for your help Michel, that gives me the confidence I need.
Re: Inner Join - foreign keys are in a range [message #252458 is a reply to message #252299] Wed, 18 July 2007 22:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It may return multiple rows per ITEM though if you have overlapping ranges. If you just want one row per item, you will need to identify which of many matching ranges you want to be the one returned.

Something like this (untested!) might work:
SELECT *
FROM (
  SELECT item.*, item_range.*,
         ROW_NUMBER() 
          OVER (
           PARTITION BY item.itm_id 
           ORDER BY item_range.itm_id_start 
          ) AS rn
  FROM item, item_range
  WHERE item.itm_id BETWEEN item_range.itm_id_start AND item_range.itm_id_end
)
WHERE rn = 1
/

You could also do it with a sub-query.

Ross Leishman
Re: Inner Join - foreign keys are in a range [message #252502 is a reply to message #252458] Thu, 19 July 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It may return multiple rows per ITEM though if you have overlapping ranges

If this is the case then I tend to think the model is unmanageable and will lead to undeterministic results.

Regards
Michel
Re: Inner Join - foreign keys are in a range [message #252542 is a reply to message #252502] Thu, 19 July 2007 02:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, but when was the last time that stopped a model being implemented?
Re: Inner Join - foreign keys are in a range [message #252576 is a reply to message #252245] Thu, 19 July 2007 03:50 Go to previous messageGo to next message
benzene
Messages: 3
Registered: July 2007
Junior Member
Good call rleishman ... In this case however, the ranges will not be overlapping, the app code should take care of that.

Although an unmanageable model together with overcomplex app code can make life "interesting" ...
Re: Inner Join - foreign keys are in a range [message #252584 is a reply to message #252576] Thu, 19 July 2007 05:05 Go to previous message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"interesting" is THE word. Cool

Regards
Michel
Previous Topic: prior clause confusion
Next Topic: How to UPDATE a table A base on table B condition matched with table A?
Goto Forum:
  


Current Time: Mon Dec 05 05:11:19 CST 2016

Total time taken to generate the page: 0.04537 seconds