johnirl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
here's my problem.

Say you have two SQL statements. Imagine that the first one outputs the values for the columns A and B.

<code>Select A, B from table where something;<code>

<code> A B
- -
4 5
2 3
6 7 <code>

Now in my second SQL statement I want to get C such that A and B equals the values they returned above.

<code>select C from table where A=4 and B=5;
select C from table where A=2 and B=3;
select C from table where A=6 and B=7;
<code>

Anybody have an idea how I might implement this?
I'm using the DBI module for retrieving the results and most of my DBI info has been gottn from here and here

  • Comment on Pipe the Output of a SQL statement into the Input of another

Replies are listed 'Best First'.
Re: Pipe the Output of a SQL statement into the Input of another
by Abigail-II (Bishop) on Jul 15, 2002 at 15:03 UTC
    Use something like: 
    select  distinct (s.C)
  from  TTTT as t, TTTT as s    
 where  something
        and s.A = t.A 
        and s.B = t.B;

    [download]
    This is the SQL dialect that MySQL speaks. For Sybase, you would probably use something like: 
    select  distinct (s.C)
  from  TTTT.t, TTTT.s          
 where  something
        and s.A = t.A 
        and s.B = t.B;

    [download]
    But I don't have a server to test this on.

    You might want to edit the "something" to explicitely mention the table alias t.

    Abigail

[reply]
[d/l]
[select]
Re: Pipe the Output of a SQL statement into the Input of another
by Aristotle (Chancellor) on Jul 15, 2002 at 16:27 UTC
    Or even simpler yet SELECT c FROM table WHERE something?

    Makeshifts last the longest.

[reply]
Re: Pipe the Output of a SQL statement into the Input of another
by amphiplex (Monk) on Jul 15, 2002 at 16:03 UTC
[reply]
[d/l]
      Consider the following table: 
       
   A  B  C  D
 
   4  5  1  7
   4  5  2  8
      And the where clause being D = 7. Then your select would return just one line, and miss the C = 2. If you first select the A = 4, B = 5, and then go for the C, you get both lines.

      Abigail

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom