This seems like it would be a common task with an easy solution but I've come up empty handed both on StackOverflow and Google.
Scenario is this: I have two tables A & B that share a many-to-many relationship. As such I have table A_B with foreign keys which maps the A-to-B record relationships. Standard stuff.
All I'm trying to figure out is how to query the tables before I enter a new record (one 'A' record with one or more 'B' records) if a matching, identical relationship already exists. The goal is to not duplicate the data.
Finally, these tables will grow quite large so I need to keep overhead down where at all possible.
UPDATE
Below is an example query I was trying, to determine if there was an existing A record mapped to B_id values 3, 4, and 5. It works, however it returns false positives if there are two different A_id values that span 3, 4, and 5, e.g.:
SELECT A_id, B_id
FROM A_B
GROUP BY
A_id HAVING
B_id IN (3,4,5)
AND
COUNT(*) = 3
LIMIT 1
UPDATE 2
The A_B table's primary key is a composite key including A_id and B_id.
The A_B table defines a unique composite key including A_id and B_id.
A single A is comprised of one or more Bs.
A more general way to phrase this question: given a finite set of B id values, I need to be able to determine if there is an existing A comprised of that exact set of Bs. If not, a new A is created with that set of Bs.
Cheers
GROUP_CONCAT to the rescue! Given B_id values 1, 3 and 5, here's how you (I) can determine if that unique combination exists and retrieve the corresponding A_id at the same time:
SELECT A_id FROM A_B
GROUP BY A_id
HAVING GROUP_CONCAT(B_id) = '1,3,5';
No subqueries required, and may be optimized by adding a composite index A_id_B_id on the A_B table.
Thanks to participants who chimed in and ultimately steered me in the right direction.
Cheers
Why not just query the table to see if there are existing records?
$query = "SELECT * from tableA_B WHERE columnA = A"
$result = mysql_query($query);
if( mysql_num_rows($result) > 1){
//do something about having entries
}
You'll obviously have to replace the table and column names with the actual values as well as your search parameter for A.
Do you really have foreign keys? There should be a way to declare this combination of table1.key-->table2.key unique. Which would result in a regular SQL error, when producing an already existing pair of entries. This is what i like about foraign keys. It's very clean, because the intelligence on (not)allowed entries stays on database level.
You have a table A_B containing assignments, don't you? A.id 88 belongs to B.id 99 ... etc.?
And you plan inserting ONE A-Record combined with n B-Records? Why not - if new values are A=99 and B: 10, 11, 12, 17, 18, 20
SELECT b_id
FROM A_B
WHERE a_id = 99
AND b_id IN (10, 11, 12, 17, 18, 20);
This will result in a list of b_ids you must not insert again ... OR an empty result (all entries are new).