Select based on list of pairs

netezzaqueryselect

I have a table with two main columns, id1 and id2:

drop table if exists pairs;
create table pairs (id1 int, id2 int);
insert into pairs values
    (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8);

I want to select rows which have these pairs [(1,2),(5,4),(3,1)]. This array could have more than 500 items, so how I can write a select query to select those rows?
I am using 'Netezza' database but I need to find the logic to do this, I can figure out how to implement this logic in my database engine.

Best Answer

I would start with:

SELECT id1, id2
FROM pairs
WHERE id1 IN (1,5,3) AND id2 IN (2,4,1)

The '1,5,3' and '2,4,1' can also be results from another query.

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

MySQL – How to Perform a Query on a Dynamic List of Tables

All of this could have been accomplished with the INFORMATION_SCHEMA database

The table names are in the INFORMATION_SCHEMA

SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack';

You could then line up the tables that exist against the tabidx table

SELECT B.table_name FROM (SELECT date FROM tabidx) A
INNER JOIN (SELECT table_name FROM information_schema.tables
WHERE table_schema='conntrack') B ON A.date = B.table_name;

With these table_names, setup a long query against all tables in conntrack

PROPOSED QUERY

SET @fromdate = '2015-04-25';
SET @todate = CURRENT_DATE();
SET @given_username='L001000';
SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ')
    INTO @sql
FROM
(
    SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
    ',username,''%'' srcip,''%'' scport FROM `',table_name,
    '` WHERE username=',QUOTE(@given_username)) qry
    FROM
    (
        SELECT BB.table_name FROM
        (
            SELECT date FROM tabidx
            WHERE date>=@fromdate AND date<=@todate
        ) AA
        INNER JOIN
        (
            SELECT table_name FROM information_schema.tables
            WHERE table_schema='conntrack'
        ) BB ON AA.date = BB.table_name
    ) A
) B;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SAMPLE DATA (From Your Question)

DROP DATABASE IF EXISTS conntrack_sample;
CREATE DATABASE conntrack_sample;
USE conntrack_s
CREATE TABLE tabidx (date date, PRIMARY KEY (date));
INSERT INTO tabidx VALUES 
('2015-04-25'),('2015-04-26'),('2015-04-27'),
('2015-04-28'),('2015-04-29');
SELECT * FROM tabidx;
CREATE TABLE `0000-00-00`
(
    time time,username CHAR(7),
    srcip CHAR(15),scport INT,
    PRIMARY KEY (username,time)
);
CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
INSERT INTO `2015-04-25` VALUES
('19:20:00','L001000','10.10.10.1',1304),
('19:20:00','L001001','10.10.10.2',1640),
('19:20:01','L001002','10.10.10.3',2001);
INSERT INTO `2015-04-26` VALUES
('19:21:00','L001000','10.10.10.1',1304),
('19:21:00','L001001','10.10.10.2',1640),
('19:21:01','L001002','10.10.10.3',2001);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS conntrack_sample;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE conntrack_sample;
Query OK, 1 row affected (0.00 sec)

mysql> USE conntrack_sample
Database changed
mysql> CREATE TABLE tabidx (date date, PRIMARY KEY (date));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tabidx VALUES
    -> ('2015-04-25'),('2015-04-26'),('2015-04-27'),
    -> ('2015-04-28'),('2015-04-29');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tabidx;
+------------+
| date       |
+------------+
| 2015-04-25 |
| 2015-04-26 |
| 2015-04-27 |
| 2015-04-28 |
| 2015-04-29 |
+------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE `0000-00-00`
    -> (
    ->     time time,username CHAR(7),
    ->     srcip CHAR(15),scport INT,
    ->     PRIMARY KEY (username,time)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `2015-04-25` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `2015-04-26` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `2015-04-27` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `2015-04-28` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `2015-04-29` LIKE `0000-00-00`;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `2015-04-25` VALUES
    -> ('19:20:00','L001000','10.10.10.1',1304),
    -> ('19:20:00','L001001','10.10.10.2',1640),
    -> ('19:20:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `2015-04-26` VALUES
    -> ('19:21:00','L001000','10.10.10.1',1304),
    -> ('19:21:00','L001001','10.10.10.2',1640),
    -> ('19:21:01','L001002','10.10.10.3',2001);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @fromdate = '2015-04-25';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @todate = CURRENT_DATE();
Query OK, 0 rows affected (0.02 sec)

mysql> SET @given_username='L001000';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     GROUP_CONCAT(qry SEPARATOR ' UNION ')
    ->     INTO @sql
    -> FROM
    -> (
    ->     SELECT CONCAT('SELECT ',QUOTE(table_name),' as "Date and Time"',
    ->     ',username,''%'' srcip,''%'' scport FROM `',table_name,
    ->     '` WHERE username=',QUOTE(@given_username)) qry
    ->     FROM
    ->     (
    ->         SELECT BB.table_name FROM
    ->         (
    ->             SELECT date FROM tabidx
    ->             WHERE date>=@fromdate AND date<=@todate
    ->         ) AA
    ->         INNER JOIN
    ->         (
    ->             SELECT table_name FROM information_schema.tables
    ->             WHERE table_schema='conntrack_sample'
    ->         ) BB ON AA.date = BB.table_name
    ->     ) A
    -> ) B;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

PROPOSED QUERY OUTPUT

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+---------------+----------+-------+--------+
| Date and Time | username | srcip | scport |
+---------------+----------+-------+--------+
| 2015-04-25    | L001000  | %     | %      |
| 2015-04-26    | L001000  | %     | %      |
+---------------+----------+-------+--------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

NOTE: Here is what @sql has

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT '2015-04-25' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-25` WHERE username='L001000' UNION SELECT '2015-04-26' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-26` WHERE username='L001000' UNION SELECT '2015-04-27' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-27` WHERE username='L001000' UNION SELECT '2015-04-28' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-28` WHERE username='L001000' UNION SELECT '2015-04-29' as "Date and Time",username,'%' srcip,'%' scport FROM `2015-04-29` WHERE username='L001000'
1 row in set (0.00 sec)

mysql>