CODEWITHSUNDEEP
c#sql
Sona
Sona

Reputation: 23

How to get common data from same table with different conditions in c# or sql

I have 2 tables, one table consist of Items having columns itemID, name

other table is Location having columns as ItemID, LocationID Each Item can have multiple locations For eg: I have 3 items Mouse, Keyboard, Speaker

Now i want to have a code in c# or sql which will give me common locations of all the 3 items i.e A in the current example.

Upvotes: 2

Views: 364

Answers (4)

mnzhr
mnzhr

Reputation: 9

DECLARE @Locations TABLE (
ItemId INT,
Location VARCHAR(5)
)


DECLARE @Items TABLE (
ItemId INT,
Name VARCHAR(50)
)

INSERT INTO @Items (ItemId,Name)VALUES(1,'Mouse')
INSERT INTO @Items (ItemId,Name)VALUES(2,'Keyboard')
INSERT INTO @Items (ItemId,Name)VALUES(3,'Speaker')


INSERT INTO @Locations (ItemId,Location) VALUES(1, 'A')
INSERT INTO @Locations (ItemId,Location) VALUES(1, 'B')
INSERT INTO @Locations (ItemId,Location) VALUES(1, 'C')
INSERT INTO @Locations (ItemId,Location) VALUES(1, 'D')

INSERT INTO @Locations (ItemId,Location) VALUES(2, 'A')
INSERT INTO @Locations (ItemId,Location) VALUES(2, 'C')

INSERT INTO @Locations (ItemId,Location) VALUES(3, 'A')
INSERT INTO @Locations (ItemId,Location) VALUES(3, 'B')
INSERT INTO @Locations (ItemId,Location) VALUES(3, 'C')
INSERT INTO @Locations (ItemId,Location) VALUES(3, 'C')


SELECT List.Location FROM (
SELECT l.Location,l.ItemId
from @Locations l
GROUP by l.Location,l.ItemId) List
GROUP by List.Location
having count(List.Location) = (select count(*) from @Items);

Can you try this?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want:

select l.locationid
from locations l
group by l.locationid
having count(*) = (select count(*) from items);

This assumes that the rows are unique in locations. Otherwise, use count(distinct itemid).

Here is a db<>fiddle illustrating that it works.

Upvotes: 3

Jhonatan Piero Abarca Murillo
Jhonatan Piero Abarca Murillo

Reputation: 81

You can try using PIVOT in SQL Server, maybe it can works for you. 

CREATE TABLE #items (
  ID_item int,
  name varchar(20)
)
CREATE TABLE #item_location (
  ID_item int,
  ID_location char(1)
)

INSERT INTO #items( ID_item, name ) VALUES (1,'Mouse')
INSERT INTO #items( ID_item, name ) VALUES (2,'Keyboard')
INSERT INTO #items( ID_item, name ) VALUES (3,'Speaker')

INSERT INTO #item_location (ID_item, ID_location) VALUES (1,'A')
INSERT INTO #item_location (ID_item, ID_location) VALUES (1,'B')
INSERT INTO #item_location (ID_item, ID_location) VALUES (1,'C')
INSERT INTO #item_location (ID_item, ID_location) VALUES (1,'D')
INSERT INTO #item_location (ID_item, ID_location) VALUES (2,'A')
INSERT INTO #item_location (ID_item, ID_location) VALUES (2,'C')
INSERT INTO #item_location (ID_item, ID_location) VALUES (3,'A')
INSERT INTO #item_location (ID_item, ID_location) VALUES (3,'B')
INSERT INTO #item_location (ID_item, ID_location) VALUES (3,'C')


--SELECT B.ID_location, COUNT(1) 
--FROM #items AS A
--INNER JOIN #item_location AS B ON A.ID_item = B.ID_Item
--GROUP BY B.ID_Location;

DECLARE @sql NVARCHAR(max);
DECLARE @columns VARCHAR(200);

SET @columns = STUFF(
     (
     SELECT
       ','+ QUOTENAME(LTRIM(name))
     FROM
       (SELECT DISTINCT
         A.name
        FROM #items AS A
       ) AS T 
     FOR XML PATH('')
     ), 1, 1, '')

 SET @sql = N'
  SELECT
   * 
  FROM
  (  
  SELECT  ID_location,name
  FROM #item_location A INNER JOIN #items B ON B.ID_item = A.ID_item 
  ) AS T
  PIVOT   
  (
  count(name)
  FOR name IN (' + @columns + N')
  ) AS P order by ID_location;'; 

 EXEC sp_executesql @sql;

  DROP TABLE #item_location
  DROP TABLE #items

Upvotes: 0

MiltonSwingline
MiltonSwingline

Reputation: 71

Try LINQ union:

int[] ints1 = { 5, 3, 9, 7, 5, 9, 3, 7 };
int[] ints2 = { 8, 3, 6, 4, 4, 9, 1, 0 };

IEnumerable<int> union = ints1.Union(ints2);

Documentation

Upvotes: 0

Related Questions