Обсуждение: SQL Question

A SQL Question...

Bear with me... I'm kinda new here.

I am trying to query table1, selecting records that do NOT have a relation in a subset from table3 that is related to
table2.


Example:

table1(Trick table)
 1_id,trickName,trickDescription

table2(Magician table)
 2_id,lastName,firstName

table3(Trick-Magician-Knows table)
 2_id,1_id

if I choose some magician I want a list of tricks he/she DOES-NOT know.

thanks for any insight !

On Sat, 10 Nov 2001, Brian wrote:

> A SQL Question...
> I am trying to query table1, selecting records that do NOT have a relation in a subset from table3 that is related to
table2.

You lost me there.

> if I choose some magician I want a list of tricks he/she DOES-NOT know.

You basically want an outer join.
I am sort of a newbie on SQL so I am not sure on the syntax.
Will try to see if I can send you an example later.

On Sat, 10 Nov 2001, Brian wrote:

>
> I am trying to query table1, selecting records that do NOT have a relation in a subset from table3 that is related to
table2.

I tried, but couldn't figure it out.

I think the solutions is along these lines:
-Make a query which will return the combination of all names and tricks.
Something like "select tricks.trick, names,name from tricks,names;"
-Store the result of the query above into a table
-Do an outer join of tricks and the "complete" table for the instances
where the name is null.

At 18:26 10/11/01, you wrote:
>A SQL Question...
>
>Bear with me... I'm kinda new here.
>
>I am trying to query table1, selecting records that do NOT have a relation
>in a subset from table3 that is related to table2.
>
>
>Example:
>
>table1(Trick table)
>  1_id,trickName,trickDescription
>
>table2(Magician table)
>  2_id,lastName,firstName
>
>table3(Trick-Magician-Knows table)
>  2_id,1_id
>
>if I choose some magician I want a list of tricks he/she DOES-NOT know.


Interesting question! I'm no SQL expert either... I would use something like:

SELECT one_id FROM table1 EXCEPT SELECT one_id FROM table3 WHERE
table3.two_id = table2.two_id AND table2.lastName = '<name of magician to
test for>';

which does generate the correct results, but there are probably more
elegant ways of doing it.

best,

Mo



Mo Holkar
Digital Mind Games -- log on to take over
mo.holkar@digitalmindgames.com
http://www.digitalmindgames.com

Is there a way to read in XML data to postgresql?

Joe,

> Is there a way to read in XML data to postgresql?

Yes.  Use Java or Perl.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

What is the correct way to order the sequence join statements?

Device is the table driving this example join... it has foreign keys to the tables part and traveler.


SELECT *
FROM Device, Part, Traveler
Where Lot = 'LotID'

AND Part.Part_ID         = Device.Part_ID
AND Traveler.Traveler_ID = Device.Traveler_ID

-OR- do you turn around the Joins like this?

AND Device.Part_ID     = Part.Part_ID
AND Device.Traveler_ID = Traveler.Traveler_ID

thanks for the advice.
Brian

Brian <Brrrian@Excite.com> writes:
> AND Part.Part_ID         = Device.Part_ID
> AND Traveler.Traveler_ID = Device.Traveler_ID

> -OR- do you turn around the Joins like this?

> AND Device.Part_ID     = Part.Part_ID
> AND Device.Traveler_ID = Traveler.Traveler_ID

The above should be equivalent.  If you have an example where it makes a
difference, then it's probably a bug --- let's see the details.

            regards, tom lane