Re: Simple Query Issues (Improved Grammer)
Re: Simple Query Issues (Improved Grammer)
- Subject: Re: Simple Query Issues (Improved Grammer)
- From: Brian Allen <email@hidden>
- Date: Mon, 31 Mar 2003 16:45:54 -0600
It should have been distinct as the following shows:
SELECT DISTINCT A.item1, A.item2, A.item3 FROM A,B WHERE A.item1 <> B.item1
on 3/31/03 2:38 PM, Chris Giordano at email@hidden wrote:
> My question here is, does the query specified really answer the
> question being asked?
>
> What the query is giving here is not "what items from A don't have a
> related object in B", but give me all items from A times the number of
> objects in B that don't match A. To illustrate:
>
> Table A: Table B:
> item1 ... item1 ...
> ----- -----
> 1 1
> 2 3
> 3 5
> 4 7
> 5
>
> The results will be (1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5)
> because the 1 in A will match to the 3, 5 and 7 in B, the 2 will match
> all items in B, etc....
>
>
> If the question being asked is really "what items in A do not have a
> corresponding item in B" using item1 as the definition of
> correspondence (i.e., select * from A where item1 not in (select item1
> from B)) then it's a different story, and will depend on how item1 is
> defined in the two tables and how it is tracked by WebObjects.
>
> Being relatively inexperienced in how WebObjects is going to handle
> this (given that I've had WebObjects for all of about a month, now),
> I'd see a number of different scenarios:
>
> 1. item1 is an arbitrary field in A and B that has no relation to the
> key in either table.
> 2. item1 is part of a key in B with a to-many relationship from A to B.
> 3. item1 is a the primary key of B, with a to-one relationship from A
> to B.
>
> For case 1, I don't see much else to do other than build some custom
> SQL or some similarly inelegant method (from my perceptions of how WO
> is intended to be used) such as for each item in A building qualifier
> and fetching the matching objects from B and noting the cases where no
> objects are returned. For cases 2 and 3, I assume there would be some
> way of checking that no related instances exist (i.e., a null or 0
> somewhere, such as the result of A.storedValueForKey("B_items") where
> B_items is a relationship from A to B ).
>
>
> Again, I'm no expert with WebObjects, but I done enough database work
> (it's most of what they pay me for, after all) that the mismatch
> between the query and the question bothered me. When stated in natural
> language rather than in code, the question doesn't seem to me to be
> that complicated to implement. But then again, I haven't tried it yet
> so I wouldn't know if it really is or not.
>
> Anyone out there more knowledgeable than me care to comment?
>
> chris
>
>
>
> On Monday, March 31, 2003, at 02:24 PM, Art Isbell wrote:
>
>> On Sunday, March 30, 2003, at 05:22 PM, I wrote:
>>
>>> On Sunday, March 30, 2003, at 08:54 AM, Brian Allen wrote:
>>>
>>>> I have not been able to determine how to do some basic queries within
>>>> WebObjects that can be easily done using SQL.
>>>>
>>>> For example, two tables A and B with a number of columns.
>>>>
>>>> SELECT A.item1, A.item2, A.item3 FROM A,B WHERE A.item1 <> B.item1
>>>>
>>>> The query should return all of the records within table A that did
>>>> not have
>>>> any matches between the A.item1s and the B.item1s.
>>>
>>> The above query doesn't specify any relationship between entities A
>>> and B.
>>
>> Upon further consideration, I suppose the above query does specify a
>> relationship between entities A and B. However, the relationship is
>> based upon the inequality of attributes whereas EOF's relationships
>> are based on the equality of attributes. I don't know of a way to
>> define a relationship in EOF based on the inequality of attributes.
>> Suggestions?
>>
>> Aloha,
>> Art
> _______________________________________________
> webobjects-dev mailing list | email@hidden
> Help/Unsubscribe/Archives:
> http://www.lists.apple.com/mailman/listinfo/webobjects-dev
> Do not post admin requests to the list. They will be ignored.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.