Need query help in one to many relationship

[hereme]

I have these tables

Boxes:
id | name
1 | oranges
2 | mangoes
3 | Bananas
4 | misc

thingsinboxes:
id | box_id | thing
1 | 1 | a
2 | 1 | b
3 | 2 | c
4 | 3 | d
5 | 3 | e
6 | 4 | a
7 | 4 | b
8 | 4 | c


How do i get thingsinboxes.box_id that has thing='a' AND thing='b'

i dont want anything returned when i just say thing ='a' because i am leaving out thing='b'

[jmcilhinney]

First up, there are no records with thing = 'a' AND thing = 'b'. The correct terminology here is OR:

SELECT Boxes.name, thingsinboxes.thing
FROM Boxes INNER JOIN thingsinboxes
ON Boxes.id = thingsinboxes.box_id
WHERE thing = 'a' OR thing = 'b'

Secondly, I'm afraid that this doesn't make sense, to me at least:

i dont want anything returned when i just say thing ='a' because i am leaving out thing='b'

You aren't "saying" anything. You're executing code. Vague terms like that don't really mean anything to anyone who doesn't know your project. If the user is supposed to enter data into your app then you need to explain that. If you want to hard-code values into the SQL code then you need to explain that. Provide a full and clear description each and every time to avoid confusion and time wasted by our having to ask for more information.

[hereme]

SELECT Boxes.name, thingsinboxes.thing
FROM Boxes INNER JOIN thingsinboxes
ON Boxes.id = thingsinboxes.box_id
WHERE thing = 'a' OR thing = 'b'

thanks for the response. But what is the query to return 1 when i need to get a result only if thing='a' and thing='b' ? That is two conditions. i have two dropdowns and i need to get boxes.id when only both the conditions are met.

[si_the_geek]

So, what you mean is that you want to get the ID where there is a thingsinboxes record for 'a' and a thingsinboxes record for 'b'

There are various ways to do that kind of thing, including this:

SELECT box_id
FROM thingsinboxes
WHERE box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
  AND box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')

...with the box info too:

SELECT id, name
FROM boxes
WHERE id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
  AND id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')

[hereme]

ok we are getting closer.

what if i dont want anything returned for this -
SELECT box_id
FROM thingsinboxes
WHERE box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')

but i want 1 returned for this -
SELECT box_id
FROM thingsinboxes
WHERE box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
AND box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')

the problem with first query is it will return a result even though i want a result ONLY if all the box_id s are passed.

thank you for your patience

[si_the_geek]

I don't know where that first query came from, or why you posted it.

Have you tried running the query I posted?

[hereme]

I don't know where that first query came from, or why you posted it.

Have you tried running the query I posted?

yes. your query works. but that is not what i am looking for. I am looking for a result ONLY if both the conditions are passed. If only one condition is passed, i do not want a result.

thanks

[si_the_geek]

I'm afraid I don't see any difference between your explanation and what the query should be doing.

Can you show us the results of the query I posted, and the results you actually want to see?

[hereme]

SELECT box_id
FROM thingsinboxes
WHERE box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
  AND box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')

that is valid if i am passing both 'a' and 'b' . but i want to get the result ONLY when BOTH are passed.

I want to get no result if i do just

WHERE box_id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')

[si_the_geek]

Why do you want to run anything unless both values are available?

Based on what you have posted, you should only be running the query when both drop-downs have a selection.

[Pradeep1210]

Just as you showed the table data in psot #1, can you also show the expected result?

[hereme]

Just as you showed the table data in psot #1, can you also show the expected result?

id | name
1 | oranges

this is the result of query that passes both 'a' and 'b'

[Pradeep1210]

Si's query works great. All you need to do is pass an empty string appropriately, when one of the parameters is missing. (i.e. Pass the values of the combobox; if they have nothing pass nothing.)

e.g.
If you have both 'a' and 'b' then:

SELECT id, name
FROM boxes
WHERE id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
  AND id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')

It will return appropriate result.

Otherwise if suppose 'a' is missing then we pass empty string instead of 'a'.
likewise, if 'b' is missing then we pass empty string instead of 'b'

--returns empty resultset
SELECT id, name
FROM boxes
WHERE id IN (SELECT box_id FROM thingsinboxes WHERE thing = '')
  AND id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'b')
 
--returns empty resultset
SELECT id, name
FROM boxes
WHERE id IN (SELECT box_id FROM thingsinboxes WHERE thing = 'a')
  AND id IN (SELECT box_id FROM thingsinboxes WHERE thing = '')

Both of the above queries will return no rows if either 'a' or 'b' is missing.