I've got one of those problems where I've looked at a query too long and now I
can't see anything clearly any more. I've put the table layout below of the
columns in question (there are more, but they're not important here).
ENTITY
------------
+ID
+LOCATION
+PARENT_ID
ENTITY_NAME
------------
+ID
+ENTITY_ID
+ENTITY_NAME
+START_DATE
+END_DATE
So, an entity can change names over time, but can only ever have one currently
active name determined by the start and end dates. It's very easy to get the
entity's current name. I've no problem with that. The issue I'm having is
trying to get the PARENT ENTITY'S name. It's easy to get the currently active
parent's name as well, but where I'm messing up is that I'm leaving out those
records which DO NOT HAVE a parent entity assigned to them.
My query:
SELECT A.ID , B.ENTITY_NAME, C.ENTITY_NAME AS PARENT_NAME
FROM ENTITY A, ENTITY_NAME B,
(SELECT entity_name, entity_id from entity_name where ((NOW() BETWEEN
START_DATE AND END_DATE) OR (now() > START_DATE AND END_DATE is NULL))) as C
WHERE A.ID = B.ENTITY_ID AND A.PARENT_ID = C.ENTITY_ID AND
(
((NOW() BETWEEN B.START_DATE AND B.END_DATE) OR (now() >
B.START_DATE AND B.END_DATE is NULL))
)
DATA RETURNED
---------------------------------------------------
ID ENTITY_NAME PARENT_NAME
---------------------------------------------------
5 Shipper B Shipper A-2
4 Shipper A-2 Shipper B
8 Test Vector US Pipeline
---------------------------------------------------
DATA THAT SHOULD BE RETURNED
---------------------------------------------------
ID ENTITY_NAME PARENT_NAME
---------------------------------------------------
3 Vector US Pipeline
5 Shipper B Shipper A-2
6 Agent 1
4 Shipper A-2 Shipper B
8 Test Vector US Pipeline
---------------------------------------------------
I started out using a join, but then switched to the subquery... Like I said,
it shouldn't be all that difficult, but I just keep messing with it and not
getting it right.
Thanks,
Ferg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210193
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
