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

hi,

my question is : how can i select row of data in db and then evaluate it. example:

i have two tables tableA and table B. and now i want to select every row in tableA and search if the data exists in tableB. if xists then do something with it (it doesn't matter what --> count) 

select count (*) from tableA where tableA = tableB;

[download]
and i want to do this for every row. 
something like pseudocode:

foreach my $data(tableA){
                my $sth = "select count * from tableA where tableA = t
+ableB";
                do_it ($sth);
                
}

[download]
i don't know if i was clear enough, but if i wasn't tell me and i'll try to elaborate.

thanks,

robert

Replies are listed 'Best First'.
Re: perl dbi select question
by Anonymous Monk on May 12, 2008 at 12:50 UTC

    What you are trying to do in the first query is called a INNER JOIN, so the query would be something like:

    select count(*) from tableA
    INNER JOIN tableB ON (tableA.tableAkey = tableB.tableBkey)

    Here's the skinny:   when you want to get information from two or more tables, you do this by joining them, which is based on fields in the two tables (e.g. “table1key, table2key”, for example ... use any existing field-name(s) you have). For each unique value occurring (for an INNER join) in both tables, every occurrence of that value in one table will be associated with every occurrence of that value in the other, a so-called Cartesian product.

    Conceptually, the “rest” of the actions requested by the query are then applied against the join-result.

[reply]
      ok

      why do you prefere inner join over select, i mean i was talking to a lot of people over some issues concerning the sql and everybody preferred join two tables over "select * from...where..."

      is that better , faster, or just a different school.

      for example i could do it by writing

      select ... from TableA where y in (select ... from TableB)... or something like this

      thanx

[reply]
      To clarify, an INNER JOIN is equivalent to a SELECT from multiple tables with a WHERE clause, i.e. these are the same: 
       select count(*) from tableA
INNER JOIN tableB ON (tableA.tableAkey = tableB.tableBkey)

 select count(*) from tableA, tableB
 WHERE tableA.tableAkey = tableB.tableBkey

      [download]
[reply]
[d/l]
Re: perl dbi select question
by psini (Deacon) on May 12, 2008 at 12:47 UTC

    Not too clear... :) Tell me if I did understand.

    You have two tables (tableA and tableB) and wou need to match rows from tableA with rows from tableB. To do so I assume there is a column (say 'foo') in tableA you want to match with another column (sat 'bar') in tableB .

    If all you need is to know how many rows in tableB match each row in tableA, the following SQL should be enough:

    SELECT *,(SELECT count(*) FROM tableb WHERE tablea.foo=tableb.bar) FROM tablea

    Rule One: Do not act incautiously when confronting a little bald wrinkly smiling man.
[reply]
[d/l]
      that is it !
[reply]
Re: perl dbi select question
by apl (Monsignor) on May 12, 2008 at 12:43 UTC
    What fields in a record in TableA need to exist in a record in TableB to constitute identify? you can do the test in your SQL by (for example): 
    my $sth = "select count * from tableA where tableA.key1 = tableB.key2"
+;

    [download]

    Obviously this is not the full query, as TableB is not defined.

[reply]
[d/l]
      ok

      let say that tableA has 2 columns x and y and let say that data in column y is corresponds to data in tableB (only one column z). now what i want to do is to pick every data in column x that has data y in tableB column z. and if it does than print it to a file.

      printing is not the problem, the problem is to check it for every record and then evaluate it to truth, so if it is truth than do something with it but if it is not skip it.

      thanx

[reply]

        ok, so you need a full join:

        SELECT DISTINCT tableA.x FROM tableA,tableB WHERE tableA.y=tableB.z

        Rule One: Do not act incautiously when confronting a little bald wrinkly smiling man.
[reply]
[d/l]
Re: perl dbi select question
by grizzley (Chaplain) on May 12, 2008 at 12:51 UTC
    Start by reading e.g. DBI SQL Query Question. Look at the question to see how to run query, how to iterate over results, etc. Then prepare proper select, which returns all rows you want to operate on, and voila!
[reply]
Re: perl dbi select question - using GROUP BY
by pc88mxer (Vicar) on May 12, 2008 at 16:20 UTC
    I'd probably use this approach: 
    SELECT tablea.id, COUNT(*) FROM tablea, tableb
  WHERE tablea.foo = tableb.bar
  GROUP BY tablea.id

    [download]
    Here the id column is some column that identifies the row. It could be the foo column or some other column or even a collection of columns. Whatever it is, just put it in the SELECT phrase in addition to the GROUP BY phrase, e.g. if your rows in tablea are identified by the columns id1 and id2, then use: 
    SELECT tablea.id1, tablea.id2, COUNT(*) FROM ...
  ...
  GROUP BY id1, id2

    [download]
    The sub-select approach will work, but databases have a lot of techniques for efficiently executing GROUP BY queries. With the sub-select approach you run the risk of explicitly telling the database how to run the query which doesn't allow it to optimize it.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom