CODEWITHSUNDEEP
sqlsql-serverone-to-manywhere-clause
Rami Sakr
Rami Sakr

Reputation: 63

How can I do a one to many Search in SQL?

Given the following

Tables:

Names
Id int
Name varchar

Properties
Id int
NameId varchar
PropertyValue int

Sample Data:

Names             Properties
Id  Name         Id    NameId     PropertyValue
1   Sam          1       1              1
2   Bam          2       1              2
3   Ram          3       2              1

I want to search through the properties table where NameId = 1 AND both criterias PropertyValue = 1 AND PropertyValue = 2 are applied

What I did was

SELECT dbo.Names.Id, dbo.Names.Name, dbo.PropertyValue
FROM dbo.Names
LEFT OUTER JOIN dbo.Properties on dbo.Names.Id = dbo.Properties.NameId
WHERE dbo.Names.Id = 1
AND dbo.Properties.PropertyValue IN (1,2)

The IN does an OR, what I need is an AND, how can I achieve so ?

EDIT:

What I want is that when both criterias are met, to return two different rows even if it has duplicated data and when one of the two criterias is NOT met, not to return anything

Upvotes: 0

Views: 210

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

The problem is that you need to operate on a set of rows for each user. Basically, you want a user that has rows with both values. Try this:

select * from properties p1
join names n on p1.nameId = n.id
where exists (
  select 1 from properties p2
  where p1.nameId = p2.nameId and p2.propertyValue in (1, 2)
  group by p2.nameId
  having count(distinct p2.propertyValue) = 2
)

Another option (based on Francis') would be:

select * from properties
join names n on p1.nameId = n.id
where nameId in (
  select nameId from properties
  where propertyValue in (1, 2)
  group by nameId
  having count(distinct propertyValue) = 2
)

But I'm not sure which one will be faster.

Upvotes: 0

Francis P
Francis P

Reputation: 13655

SELECT * FROM NAMES
WHERE ID IN (
  SELECT NAMEID FROM PROPERTIES 
  WHERE PROPERTYVALUE = 1 OR 
    PROPERTYVALUE = 2
  GROUP BY NAMEID
  HAVING COUNT(*) > 1)

Fiddle if you want to test it: http://sqlfiddle.com/#!3/23365/2

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460138

I hope this is what you want, because it's not entirely clear(imho).

SELECT n.Id, n.Name, p.PropertyValue
FROM dbo.Names n
INNER JOIN dbo.Properties p on n.Id = p.NameId 
WHERE n.ID = 1
AND EXISTS(
    SELECT null FROM Properties p2
    WHERE p2.NameId=ID AND p2.PropertyValue=1
)
AND EXISTS(
    SELECT null FROM Properties p2
    WHERE p2.NameId=ID AND p2.PropertyValue=2
)

Upvotes: 1

Teja
Teja

Reputation: 13534

SELECT dbo.Names.Id, dbo.Names.Name, dbo.PropertyValue
FROM dbo.Names
LEFT OUTER JOIN dbo.Properties on dbo.Names.Id = dbo.Properties.NameId
WHERE dbo.Names.Id = 1
AND dbo.Properties.PropertyValue=1
UNION
SELECT dbo.Names.Id, dbo.Names.Name, dbo.PropertyValue
FROM dbo.Names
LEFT OUTER JOIN dbo.Properties on dbo.Names.Id = dbo.Properties.NameId
WHERE dbo.Names.Id = 1
AND dbo.Properties.PropertyValue=2

Upvotes: 0

Related Questions