Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Question

SQL Question

From
Brian
Date:
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 !

Re: SQL Question

From
Francisco Reyes
Date:
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.

Re: SQL Question

From
Francisco Reyes
Date:
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.

Re: SQL Question

From
Mo Holkar
Date:
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

XML & Postgresql

From
Joe Moraca
Date:
Is there a way to read in XML data to postgresql?

Re: XML & Postgresql

From
"Josh Berkus"
Date:
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

join question

From
Brian
Date:
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

Re: join question

From
Tom Lane
Date:
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