On 3/6/07, Ken Dibble <[EMAIL PROTECTED]> wrote: > Right. But (he said sheepishly) my tables had actual data in them that > should have been returned in the artist field for some performances; > instead my query returned nulls in some cases.
No need to be sheepish. This stuff is hard. I suspect you have a subtle data design problem, one iirc Tamar once presented as "parents of unrelated siblings" - your Perfs table has an id that you are joining both to a PK in the Person table and a PK in the Bands table, so one of them is wrong and irrelevant. In addition, you're joining songs to perfs to persons and bands, and I can only suspect that in some cases you would get multiple results for the grandchildren in that arrangement. It's a bit hard to sort out without the data. I'd suspect it might be simpler to reconsider your design. The queries might be a lot easier if you consider a solo performer as a band of one, so all music goes through a group, but that might be a problem for some of your other tables and relationships. > If I'm understanding you > right, they weren't extra records (like one gets, for example, when one > doesn't use DISTINCT in some situations). The query failed to find data > that it should have found. And it wasn't due to truncation because I tried > it with a PADR() wrap and got the same results. Am I making sense? Yep. I've stared at your original SQL for quite some time, and I have trouble wrapping my head over exactly what it would do under some circumstances. Did I mention this stuff is hard? -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.