Below is what I came up with. It seems to work pretty well, but I'd
still welcome any advice on improvement. --Ferg
SELECT distinct A.ID , B.ENTITY_NAME,
case when A.parent_id is null then '' else C.ENTITY_NAME end 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
IF(A.parent_id is not null, A.PARENT_ID = C.ENTITY_ID,1=1) AND
((NOW() BETWEEN B.START_DATE AND B.END_DATE) OR (now() >
B.START_DATE AND B.END_DATE is NULL))
Ken Ferguson wrote:
>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
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210208
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
