CODEWITHSUNDEEP
sqlwhere-clausedb2-400
mae
mae

Reputation: 57

How to use SQL select to filter rows with category to join other (multiple) table based on condition?

I have the following 4 tables:

**TABLE.INFO**
ID_INFO|INFO_LABLE|CODE_INFO|CATEGORY
2222001|XXXXCCCAA001|88888001|ITEM
2222002|XXXXCCCAA002|88888002|PICKUP
2222003|XXXXCCCAA003|88888002|ITEM
2222004|XXXXCCCAA004|88888001|PICKUP
2222005|XXXXCCCAA005|88888001|PICKUP
2222006|XXXXCCCAA006|88888003|SUPPLIER

**TABLE.ITEM**
ID_ITEM|ID_INFO|DETAIL_ITEM|EXPIRE_DATE
A00001|2222001|COOKIE|5/12/2017
A00002|2222003|PIE|24/11/2017

**TABLE.PICKUP**
ID_PICKUP|ID_INFO|DETAIL_PICKUP|EXPIRE_DATE
P00001|2222004|MOTOR|8/12/2017
P00002|2222005|CAR|26/11/2017
P00003|2222002|TRUCK|2/10/2017

**TABLE.SUPPLIER**
ID_SUPPLIER|ID_INFO|DETAIL_SUPPLIER|EXPIRE_DATE
S00001|2222006|BANANA GREEN|8/12/2017

I want to achieve the result which selects all field on TABLE.INFO with condition only having EXPIRE_DATE < '2017-11-31' and CODE_INFO in (88888002,88888001) from join other table. ie

ID_INFO|INFO_LABLE|CODE_INFO|CATEGORY
2222002|XXXXCCCAA002|88888002|PICKUP
2222003|XXXXCCCAA003|88888002|ITEM
2222005|XXXXCCCAA005|88888001|PICKUP

How can I achieve this using join table and where clause? I have done as follows but it is not working.

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
JOIN TABLE.ITEM AS B
ON A.ID_INFO = B.ID_INFO
JOIN TABLE.PICKUP AS C
ON A.ID_INFO = C.ID_INFO
JOIN TABLE.SUPPLIER AS D
ON A.ID_INFO = D.ID_INFO
WHERE A.CODE_INFO IN (88888002,88888001) AND B.DATE_EXPIRE < '2017-11-31' OR C.DATE_EXPIRE < '2017-11-31' OR D.DATE_EXPIRE < '2017-11-31';

if each table has a large record. for sequential processes joined to each table it will make the query process time is long. Are there any other suggestions, when TABLE.INFO will join to another table (TABLE.ITEM, TABLE.PICKUP, TABLE.SUPPLIER) is determined first based on the CATEGORY field in TABLE.INFO?

Upvotes: 1

Views: 246

Answers (3)

Isuri Subasinghe
Isuri Subasinghe

Reputation: 147

You can use this query:

SELECT *
FROM   ((SELECT info.*
         FROM   info
                INNER JOIN (SELECT *
                            FROM   item
                            WHERE  expire_date < '2017-11-30') aa 
                        ON info.id_info = aa.id_info)
        UNION ALL
        (SELECT info.*
         FROM   info
                INNER JOIN (SELECT *
                            FROM   pickup
                            WHERE  expire_date < '2017-11-30') bb
                        ON info.id_info = bb.id_info)
        UNION ALL
        (SELECT info.*
         FROM   info
                INNER JOIN (SELECT *
                            FROM   supplier
                            WHERE  expire_date < '2017-11-30') cc
                        ON info.id_info = cc.id_info))
WHERE  code_info IN ( 88888002, 88888001 )

Output

Upvotes: 0

DxTx
DxTx

Reputation: 3367

Try one of these methods.
Also, I have used '2017-12-01' instead of '2017-11-31'.

SELECT info.id_info,
       info.info_lable,
       info.code_info,
       info.category
FROM   info
       LEFT JOIN item
              ON item.id_info = info.id_info
                 AND item.expire_date < '2017-12-01'
       LEFT JOIN pickup
              ON pickup.id_info = info.id_info
                 AND pickup.expire_date < '2017-12-01'
       LEFT JOIN supplier
              ON supplier.id_info = info.id_info
                 AND supplier.expire_date < '2017-12-01'
WHERE  info.code_info IN ( 88888001, 88888002 )
       AND ( item.expire_date IS NOT NULL
              OR pickup.expire_date IS NOT NULL
              OR supplier.expire_date IS NOT NULL ); 

SELECT info.id_info, info.info_lable, info.code_info, info.category
FROM   info
       INNER JOIN item
               ON item.id_info = info.id_info AND item.expire_date < '2017-12-01'
WHERE  info.code_info IN ( 88888001, 88888002 )

UNION ALL

SELECT info.id_info, info.info_lable, info.code_info, info.category
FROM   info
       INNER JOIN pickup
               ON pickup.id_info = info.id_info AND pickup.expire_date < '2017-12-01'
WHERE  info.code_info IN ( 88888001, 88888002 )

UNION ALL

SELECT info.id_info, info.info_lable, info.code_info, info.category
FROM   info
       INNER JOIN supplier
               ON supplier.id_info = info.id_info AND supplier.expire_date < '2017-12-01'
WHERE  info.code_info IN ( 88888001, 88888002 );

Upvotes: 0

Roxana Sh
Roxana Sh

Reputation: 292

try it :

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.ITEM AS B
ON A.ID_INFO = B.ID_INFO AND B.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

UNION ALL

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.PICKUP AS C
ON A.ID_INFO = C.ID_INFO AND C.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

UNION ALL

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.SUPPLIER AS D
ON A.ID_INFO = D.ID_INFO AND  D.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

Upvotes: 1

Related Questions