To retrieve information from related tables, first JOIN the tables. Here's the full code that includes the creation of the tables, the $query shows how to get the information from the database:

#!/usr/bin/perl
use warnings;
use strict;

use DBI;

my $db_file = '1.db';
unlink $db_file;

my $db = 'DBI'->connect("dbi:SQLite:dbname=$db_file", q(), q());

# Create the tables.
$db->do($_) for
    'CREATE TABLE user_tbl   (id INT, name   VARCHAR(5))',
    'CREATE TABLE topic_tbl  (id INT, topic  VARCHAR(6))',
    'CREATE TABLE detail_tbl (id INT, detail VARCHAR(7), name_id INT, 
+topic_id INT)';

# Populate the tables.
for my $insert (
                [ 'user_tbl',   [ 1, 'test1' ],  [ 2, 'test2' ] ],
                [ 'topic_tbl',  [ 1, 'topic1' ], [ 2, 'topic2' ] ],
                [ 'detail_tbl', [ 1, 'detail1', 1, 1 ],
                                [ 2, 'detail2', 1, 2 ],
                                [ 3, 'detail3', 2, 1 ] ],
               ) {
    my $table = shift @$insert;
    my $sql = "INSERT INTO $table VALUES ("
            . join(', ', map '?', 0 .. $#{ $insert->[0] }) . ')';
    my $sth = $db->prepare($sql);
    $sth->execute(@$_) for @$insert;
}

# Extract the information.
my $query = << '__SQL__';
    SELECT d.detail, u.name, t.topic
    FROM detail_tbl AS d
    JOIN user_tbl   AS u ON u.id = d.name_id
    JOIN topic_tbl  AS t ON t.id = d.topic_id
__SQL__

my $sth = $db->prepare($query);
$sth->execute;
while (my @row = $sth->fetchrow_array) {
    print "@row\n";
}
[download]

Update: if your SQLite was compiled with foreign keys support (see here), you can turn the support on and constrain your tables to avoid invalid values:

$db->do($_) for
    'PRAGMA FOREIGN_KEYS = ON',
    'CREATE TABLE user_tbl   (id INTEGER PRIMARY KEY, name  VARCHAR(5)
+)',
    'CREATE TABLE topic_tbl  (id INTEGER PRIMARY KEY, topic VARCHAR(6)
+)',
    join ' ', 'CREATE TABLE detail_tbl (id INTEGER PRIMARY KEY, detail
+ VARCHAR(7),',
              'name_id INTEGER, topic_id INTEGER,',
              'FOREIGN KEY (name_id) REFERENCES user_tbl(id),',
              'FOREIGN KEY (topic_id) REFERENCES topic_tbl(id))';
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.