SQL query to find all the states where store is not open?-CodePudding

I have table A Stores which have store information. Table B have all states information. I need to join table A to B to get all the states where store is open. Now I need to find all the states where store is in not open.

Table A:

Stores	StateCode
Store A	MP
Store B	UP
Store B	MP
Store C	JK

Table B:

StateCode	StateName
MP	Madhya Pradesh
UP	Uttar Pradesh
JK	Jammu Kashmir

Output table:

Stores	StateCode	StateName
Store A	UP	Uttar Pradesh
Store A	JK	Jammu Kashmir
Store B	JK	Jammu Kashmir
Store C	UP	Uttar Pradesh
Store C	MP	Madhya Pradesh

I have tried this:

SELECT *
FROM tableB
LEFT OUTER JOIN tableA
  ON tableA.<stateCode> = tableB.<stateCode>
WHERE tableA.<stateCode> IS NULL

CodePudding user response：

If You Use Microsoft SQL Server , Try This Query

SELECT Distinct
       A.Stores,
       B.StateCode,
       B.StateName
FROM TableA as A INNER JOIN TableB as B
ON B.StateCode Not IN (SELECT Statecode FROM TableA WHERE Stores = A.Stores)

CodePudding user response：

use sample :

SELECT
  `A`.`Stores`,
  `B`.`StateCode`,
  `B`.`StateName`
FROM
  `A`
  INNER JOIN `B` ON `B`.`StateCode` Not IN (
    SELECT
      `a1`.`Statecode`
    FROM
      `A` As `a1`
    WHERE
      `a1`.`Stores` = `A`.`Stores`
  )
GROUP By
  `A`.`Stores`,
  `B`.`StateCode`,
  `B`.`StateName`;

CodePudding user response：

Do a cross join between tableB and the distinct stores' values from tableA to get all possible combinations of (stores, states), then do a left join as the following:

select DA.Stores, B.StateCode, B.StateName
from tableB B cross join 
(
  select distinct Stores from tableA
) DA
left join tableA TA
on TA.Stores = DA.Stores and 
   TA.StateCode = B.StateCode
where TA.Stores is null
order by DA.Stores

See demo