Re: Simple Query Issues (Improved Grammer)
Re: Simple Query Issues (Improved Grammer)
- Subject: Re: Simple Query Issues (Improved Grammer)
- From: Chris Giordano <email@hidden>
- Date: Tue, 1 Apr 2003 10:30:44 -0500
Brian,
Completely different question than what I was thinking.
If I were to write a query for this, it would basically be what I wrote
in my first message:
select * from A
where item1 not in (select item1 from B)
or what Jonathan Rochkind had with a left join:
SELECT A.item1, A.item2, A.item3
FROM A LEFT OUTER JOIN B ON A.item1 = B.item1
WHERE B.item1 is null
This gives all items from A which aren't in B (which is what you're
looking for). The problem with your original query was that when
comparing every item in A with every item in B, using the "not equals"
operator would give you matches on all sorts of things that weren't
intended since it would be comparing _all_ items in A to _all_ of those
in B.
I'm not sure if there is an elegant way of doing this outside of
writing a custom query and fetching those rows or the brute force
method of fetching both tables and just comparing them. I still
haven't figured out a way to deal with a similar issue that I've come
across -- wanting to read in some translations from a lookup table
without treating them as if I'm editing them. (These are for a tool
for administering said lookup table. I've got more reading to do to
figure out whether it's really an issue in all of my cases.)
However, I think you could still do something similar with a
relationship on the tables:
Assuming you've got table A which has the items, and table B has
something other than item1 as the primary key (if it is the primary key
then this becomes somewhat trivial).
Table A: Table B:
-------- --------
item1 personID (who owns the items)
... item1 (the record assigned)
What you could do is to create a to-many relationship between table A
and table B and then limit to those records with the appropriate
personID. Then, the result of this to-many relationship should give
you the number of records for each item in A for that person. So, for
example, if you have items 1, 2, 3, 4, 5 in A, and for my person ID
there are records for items 1 and 3, the relationship, which would be
something similar to the query
select A.* (+ an array of matching items in B)
from A left join B on item1
where personID = "chris"
would give me all records in A. Then, the relying on the relationship,
you could use the storedObjectForKey("B") (given a relationship "B"
defined for table A) to determine how many items matched. When there
are no items that match, you've got a record that isn't assigned to me
yet.
I think this could probably be done in WebObjects without problem.
Hope this helps.
chris
On Tuesday, April 1, 2003, at 10:00 AM, Brian Allen wrote:
Here is a hopefully better explanation.
Table B has all of the records assigned to you. Table A has all of the
records that could be assigned to you which would includes the records
already assigned.
I want only the records not assigned to you that is the difference
between A
and B. Or another way, B's records subtracted from A's records.
- Brian Allen
on 4/1/03 8:39 AM, Chris Giordano at email@hidden wrote:
Brian,
This is still going to give you everything in A unless you've only got
one item in B, and in that case you get everything but one item in A.
It still doesn't answer what I think you want to ask, which is "how
many items in A don't have a match in B?" Is that the question that
you're trying to answer with this query?
Assuming that it is, I just did some testing (which is why it took me
overnight to get back to you -- I had to download and install the
updates for the December developer tools and Java) and using the
Authors example code as a base (from chapter 12 in the Web
Applications
book that came with WO). This has a to-many relationship from the
Authors table to the Books table. I can identify all authors with no
books by noting that the books() array will be empty when there are no
associated items.
So, although this doesn't give you the fetch specification to get what
you're looking for, it does give you a means to get the same results.
Jonathan Rochkind had some suggestions for how to achieve the same in
a
query. If you're not working with a lot of records, or they're all
loaded anyway, just looping through them, checking which ones have 0
items from B and sticking those into a secondary array would give you
something to work from.
If this isn't the question that you're trying to answer, what is
(since
all of my discussion is based on my interpretation of your question)?
chris
On Monday, March 31, 2003, at 04:04 PM, Brian Allen wrote:
Sorry, I should have indicated distinct rows as
follows:
SELECT DISTINCT A.item1, A.item2, A.item3 FROM A,B
WHERE A.item1 <> B.item1
- Brian Allen
_______________________________________________
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.