CODEWITHSUNDEEP
sqlsql-server
mithderler
mithderler

Reputation: 33

Select two different Items if they meet different condition

I have a select problem with mssql which is;

Invoice 1
ItemUniqueNo    ItemName    InvoceNo
1               A           123
3               C           123

Table A (All Invoices)
ItemUniqueNo   ItemName   InvoceNo
1               A           123
2               B           235
3               C           123

Table B (Item List)
ItemUniqueNo    ItemName    ItemGroup
1               A           XXX
2               B           YYY
3               C           ZZZ

I want to select every items (from Table A) If an invoice has both type of product which is; ItemUniqueNo=3 and ItemGroup = XXX, in same invoice. Here is my code:

SELECT
*
FROM
TABLE_A 
WHERE
(ItemUniqueNo IN (SELECT ItemUniqueNo FROM TABLE_B WHERE ItemGroup='XXX') OR ItemUniqueNo = 3)

with this code, I've listed all items which has either ItemGroup = XXX and ItemUniqueNo=3 BUT I want to meet both condition in same invoice, like:

Invoice 1
ItemUniqueNo    ItemName    InvoceNo
1               A           123  --->  This meets 'XXX' ItemGroup condition
3               C           123  --->  This meets '3' ItemUniqueNo condition

This invoice meet both 2 condition, so i want to list this two item with InvoceNo. AND all other items which meet this condition.

Upvotes: 0

Views: 778

Answers (5)

ismetguzelgun
ismetguzelgun

Reputation: 1105

You can use a case statement in your where clause instead of 'OR' then your output will be match what you did want before. You can check my answer.

Create table TableA (ItemUniqueNo int, ItemName Char(1),  InvoceNo int)
insert into TableA Values
(1, 'A', 123),
(2, 'B', 235),
(3, 'C', 123),
(4, 'A', 999),
(3, 'B', 999)

Create table TableB (ItemUniqueNo int, ItemName Char(1),  ItemGroup Varchar(10))
insert into TableB Values
(1, 'A', 'XXX'),
(2, 'B', 'YYY'),
(3, 'C', 'ZZZ'),
(4, 'A', 'XXX')

SELECT TABLEA.*
  FROM TABLEA INNER JOIN (SELECT DISTINCT TABLEA.INVOCENO
           FROM (SELECT TABLEA.INVOCENO,
                        (CASE
                            WHEN ITEMGROUP = 'XXX' THEN 1
                            WHEN TABLEA.ITEMUNIQUENO = 3 THEN 1
                            ELSE 0
                         END)
                           AS FLAG
                   FROM TABLEA
                        INNER JOIN TABLEB
                           ON CONCAT(TABLEA.ITEMUNIQUENO,TABLEA.ITEMNAME) = CONCAT(TABLEB.ITEMUNIQUENO,TABLEB.ITEMNAME))
                TABLEA
          WHERE FLAG = 1) COND ON TABLEA.INVOCENO = COND.INVOCENO

Here is a dbfiddle link

Result

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using inner join as shown below.

Create table TableA (ItemUniqueNo int, ItemName Char(1),  InvoceNo int)
insert into TableA Values
(1, 'A', 123),
(2, 'B', 235),
(3, 'C', 123)

Create table TableB (ItemUniqueNo int, ItemName Char(1),  ItemGroup Varchar(10))
insert into TableB Values
(1, 'A', 'XXX'),
(2, 'B', 'YYY'),
(3, 'C', 'ZZZ')

SELECT TableA.*
    ,TableB.ItemGroup
FROM TableA
INNER JOIN TableB ON TableA.ItemUniqueNo = TableB.ItemUniqueNo
WHERE ItemGroup = 'XXX'
    OR TableA.ItemUniqueNo = 3
ORDER BY TableA.ItemUniqueNo

Here is the live db<>fiddle demo. Another option you can try this

SELECT TableA.*
FROM TableA
INNER JOIN (
    SELECT TableA.*
    FROM TableA
    WHERE EXISTS (
            SELECT 1
            FROM TableB
            WHERE TableA.ItemName = TableB.ItemName
                AND TableA.ItemName = 'A'
            )
        AND EXISTS (
            SELECT 1
            FROM TableB
            WHERE TableA.ItemName = TableB.ItemName
                AND TableB.ItemGroup = 'XXX'
            )
    ) TEMP ON TableA.InvoceNo = TEMP.InvoceNo

Upvotes: 0

Tharuka Madumal
Tharuka Madumal

Reputation: 201

If you want to fulfill both conditions at once use AND keyword. If you want to fulfill only one condition out of two, use OR keyword. 

SELECT *
FROM TABLE_A 
WHERE ItemUniqueNo IN 
(SELECT ItemUniqueNo 
FROM TABLE_B 
WHERE ItemGroup='XXX' AND ItemUniqueNo = 3)

Upvotes: 0

Bhupendra kumar
Bhupendra kumar

Reputation: 5

Try this 

select * from [Table A] A
join [Table B]  B on A.ItemUniqueNo=B.ItemUniqueNo
where b.ItemGroup='XXX' or OR A.ItemUniqueNo = 3

Upvotes: 0

JohanB
JohanB

Reputation: 376

Just change your OR to an AND in your where clause

Upvotes: 0

Related Questions