How do I write the following mySQL query?

Question

I have a database in the following format:

idA | idB | stringValue
----+-----+------------
xyz |  1  | ANDFGFRDFG
xyz |  2  | DTGDFHFGH
xyz |  3  | DFDFDS
abc |  5  | DGDFHHGH
abc |  6  | GG
...

idA and idB together are unique.

I have a file (list.txt) with a list of id pairs like this:

xyz 2
abc 5
abc 6
...

and I would like to print it with stringValue in each row. Note some pairs of ids from the file might be absent from the DB. In that case, I don't want to print them at all.

In short, I want to filter a table using a file.

Answer 1

SELECT idA, idB, stringValue FROM table WHERE idA = 'xyz' AND idB = 12;

Would give you all columns where idA equals "xyz" and idB equals 12.

Answer 2

The number of OR clauses will be dynamic based on the number of combinations in your file.

You will probably need to make this a dynamically created query on the lines of

SELECT * FROM myTable
WHERE 
(idA = 'xyz'AND idB = '2')
OR 
(idA = 'abc'AND idB = '5')
OR
(idA = 'abc'AND idB = '6')
......

Answer 3

Some SQL clients do provide the facility that allow you to create queries or data straight from files. However I believe the better approach would be to write a script for this.

Based on other questions you recently gave like this one then perhaps the solution below in Perl may help you:

use 5.012;
use warnings;
use SQL::Abstract;

my $sql   = SQL::Abstract->new;
my @cols  = qw/ idA idB stringValue /;
my $where = build_sql_where_from_file( 'list.txt', @cols[0,1] );

my ($query, @bind) = $sql->select( 
    'yourTable',
    \@cols,
    $where,
);

sub build_sql_where_from_file {
    my $file = shift;
    my @build;

    open my $fh, '<', $file or die $!;

    for my $line (<$fh>) {
        chomp $line;
        my @fields = split / /, $line;

        push @build, {
            -and => [ 
                map { $_ => shift @fields } @_,
            ]
        };
    }

    return \@build;
}

If I now do the following using your example list.txt...

say $query;
say join ":", @bind;

then I get:

SELECT idA, idB, stringValue FROM yourTable WHERE ( ( ( idA = ? AND idB = ? ) OR ( idA = ? AND idB = ? ) OR ( idA = ? AND idB = ? ) ) )
xyz:2:abc:5:abc:6

Which is exactly what I need to then plugin straight into a DBI query.

Hope that helps.

/I3az/