I have two tables, A and B, with a relation one-to-many from A to B, using an
intermediate table A_B;
the user can select from a popup one or more elements from record B, to search
the elements in A matching with the related B elements ( I'm simplifying the
problem, thne real query is more more complex )
the user can select if the record A must match ANY of the B records selected,
and this is really simple:
having a table A_B with this content, and imagine the user has selected to
search for B_id = 1, 2, 3:
A_id | B_id
1 1
1 2
1 3
2 1
2 2
select distinct a_id from a_b where b_id in (1,2,3)
returs both records A with id 1 and 2;
But the user can select to search for A records that MATCH ALL the B records
selected, in this case the A records that are linked to ALL of record B with id
1, 2, 3
I have arranged the query this way:
select a_id from a_b where b_id in (1,2,3) group by a_id having count(b_id) = 3
this query returns only A with id 1, because checking if the count of the rows
grouped is equal to the number of the B ids specifyed in the query, I have
returned only the A records that are linked to ALL the B records selected for
the query.
Now my problem is, that for a number of reasons now I can have an A record
linked to more than one records B with the same ID, so I could have a table A_B
filled this way:
A_id | B_id
1 1
1 2
1 3
2 1
2 2
2 2
In such situation, the query above will return also the record A with ìd 2,
because count(b_id) will be equal 3, even if the A records IS NOT linked to all
the specifyed B records.
This is an unwanted result, I would like to find the way to have as a result
only the A_id 1 anyway, that is the record A that is linked to ALL the B
records specifyed.
I hope that the problem is clear, how would you solve this?
thanks in advance,
Giulio
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
