How do I join two tables together if and only if an id is linked to multiple rows in the second table in SQL Server CE?

Question

I don't know if my title correctly conveys the query I'm trying to achieve, so please edit or suggest a better one that is more correct. I'm trying to join these two tables together:

Table 1: people

id |field1|field2|
------------------
1  
2
3
4
.
.
. (etc)

Table 2: foods

person.id | name
1         | chicken
1         | pork
1         | beef
2         | pork
2         | chicken
3         | chicken
4         | beef
.
. (etc)

I need to join these tables based on a search for any people whose ID is matched with both pork or chicken. For example, if I search for pork, 1 and 2 should be returned, and if I search for pork and chicken, 1 and 2 should still be returned, but 3 will not be because it doesn't match chicken as well as pork. If I search for pork, chicken, and beef, only 1 should be returned.

I've been looking at this question, but I don't know how many items I'll be searching for (I could be searching for pork and chicken one time, and pork, chicken, or beef another time).

I started out trying the basic WHERE clause, which I was fairly certain was faulty (I confirmed this because the query didn't work):

SELECT p.id 
FROM people p, foods f 
WHERE p.id = f.person_id AND (f.name = 'pork' AND f.name = 'chicken')

but this returns no results. I seem to remember from an SQL tutorial several years ago that I should be using the EXISTS keyword along with a subquery, but I'm not sure how to shape my query/queries based on that.

EDIT: SQL Server CE doesn't support the INTERSECT keyword, so unfortunately that option isn't available to me.

Try this question: [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) Queries 1, 2, 7, 8, 10 and 11 can use a variable-size filter list. — ypercubeᵀᴹ, Jun 26 '12 at 21:50
And how are you getting the values that you need to look up?, why don't you know how many you are looking for? — Lamak, Jun 26 '12 at 21:50
@Lamak The values come from user input, so I could calculate the number of items. That's probably the only way to do this query, right? — Ricardo Altamirano, Jun 26 '12 at 21:57

score 3 · Accepted Answer · answered Jun 26 '12 at 22:11

3

If you can costruct your list ('chicken', 'pork', 'beef') and count the items in your application:

SELECT p.*
FROM   people AS p
  JOIN (
   SELECT person_id
   FROM   foods
   WHERE  name IN ('chicken', 'pork', 'beef')   --- list
   GROUP  BY person_id
   HAVING COUNT(*) = 3                          --- list size
   ) AS pf ON pf.person_id = p.id ;

answered Jun 26 '12 at 22:11

ypercubeᵀᴹ

113,259
19
174
235

Since this returns a list of person id's, I should be able to use this entire query as a subquery in a larger query, correct? I think eventually this query will be paired with another similar query, intersected (the SQLCE equivalent, that is) and used as the target of an `IN` keyword. I'll let you know tomorrow if this query is successful. – Ricardo Altamirano Jun 27 '12 at 01:19
Also thanks for posting that link in your previous comment; that's quite helpful and has a lot of good queries to study. – Ricardo Altamirano Jun 27 '12 at 01:20
@pythonscript Note this is essentially the same query you rejected in the referenced answer in your question because, "I don't know how many items I'll be searching for". – D'Arcy Rittich Jun 27 '12 at 02:05
@RedFilter You're right. I seemed to remember hearing of a better way to perform this query, without knowing the number of items, but I believe I was mistaken. I'm just waiting until I can check this query against the database to make absolutely sure. – Ricardo Altamirano Jun 27 '12 at 02:07
That worked perfectly, ypercute. Exactly what I was looking for, and it worked when I fit this query in as a tidbit of my larger overall query. Thank you! – Ricardo Altamirano Jun 27 '12 at 13:59
@pythonscript: note that this is actually Lindoff's first query (which I didn't notice when I posted). – ypercubeᵀᴹ Jun 27 '12 at 14:05
And as in my linked question, having multiple Joins or Exists (as many as the number of your items) is **faster**. – ypercubeᵀᴹ Jun 27 '12 at 14:06

score 2 · Answer 2 · answered Jun 26 '12 at 21:50

2

Use a group by instead:

select id
from foods
where name in (<list of foods>)
group by id
having count(*) = <size of list>

You can -- horror of horrors -- actually store the list in a string and get the right result. Say you have:

declare @foodlist varchar(8000) = 'pork,chicken'

Then the following query works:

select id
from foods
where charindex(','+name+',', ','+@foodlist+',') > 0
group by id
having count(*) = 1 + len(@foodlist) - len(replace(@foodlist, ',', ''))

answered Jun 26 '12 at 21:50

Gordon Linoff

1,242,037
58
646
786

Why should one use this (horror as you say)? – ypercubeᵀᴹ Jun 26 '12 at 21:53
Interesting horror, though I'm guessing the performance would suffer. – Lamak Jun 26 '12 at 21:57
1

@ypercube . . . He wants to flexibly create a list of foods. This is one way of doing so without having to modify the query. Of course, he could put the list in a temporary table. This is just an alterntive approach. – Gordon Linoff Jun 26 '12 at 22:01
1

Note this could return incorrect results if the same food is listed more than once for a person. – D'Arcy Rittich Jun 26 '12 at 22:01
@RedFilter That isn't a concern in this database, based on the way it's populated, but because I'm using SQL *Compact Edition*, which doesn't support `DECLARE`, these queries aren't an option for me. – Ricardo Altamirano Jun 26 '12 at 22:02
And also if there were spaces after the commas and in other situations. It is intended as a possible approach for solving the problem for variable numbers of foods without resorting to temporary tables, cursors, or dynamic SQL. – Gordon Linoff Jun 26 '12 at 22:03
@GordonLinoff However, it doesn't apply to SQL Compact Edition, which [doesn't support `DECLARE`](http://msdn.microsoft.com/en-us/library/ms173372%28v=SQL.110%29.aspx). – Ricardo Altamirano Jun 26 '12 at 22:04
The first query seems perfectly ok when there is a composite unique key over (person,food) in table foods. Very elegant! – Jan-Philipp Niewerth Jun 26 '12 at 22:16
@pythonscript I was referring to the first query. – D'Arcy Rittich Jun 27 '12 at 02:03
@RedFilter Ah, ok. I'll test that query as well when I have a chance tomorrow. – Ricardo Altamirano Jun 27 '12 at 02:04

score 0 · Answer 3 · edited Jun 26 '12 at 22:08

0

SELECT p.id 
FROM people p INNER JOIN foods f 
ON p.id = f.person_id 
WHERE f.name in (`list of f.name`)

If I search for pork, chicken, and beef, nothing should be returned

According to your table 1 should be returned.

edited Jun 26 '12 at 22:08

Jason Sturges

15,855
14
59
80

answered Jun 26 '12 at 22:06

KCJ

111
3

You're right, I updated the question. I'll try this query out when I have the chance. Doesn't this query return any results that match one food OR the other, though? – Ricardo Altamirano Jun 27 '12 at 13:00

How do I join two tables together if and only if an id is linked to multiple rows in the second table in SQL Server CE?

3 Answers3