To the best of my knowledge, these results (especially the data in the 'destination' field) should be the same, but I can't find out why they're different:
mysql> select * from cc_did_destination where id_cc_did=1988\G
*************************** 1. row ***************************
id: 2256
destination: SIP/t564/12505551234
priority: 1
id_cc_card: 2157
id_cc_did: 1988
creationdate: 2012-01-31 07:59:01
activated: 1
secondusedreal: 0
voip_call: 1
validated: 1
1 row in set (0.00 sec)
mysql> SELECT cc_did.did, cc_did.id, cc_did_destination.destination FROM cc_did, cc_did_destination WHERE cc_did.iduser='2157' AND cc_did_destination.id_cc_card=cc_did.id\G
*************************** 1. row ***************************
did: 7805551234
id: 1988
destination: SIP/someuser
*************************** 2. row ***************************
did: 6045551234
id: 1997
destination: SIP/macaddress-1
2 rows in set (0.01 sec)
In fact, the data for the 'destination' field is correct in the first select statement. The data in the second query is old and should have been replaced with the data we see in the first query.
As per Rachel below, what I'm trying to do is this; Join this table:
mysql> SELECT destination, id_cc_card, id_cc_did FROM cc_did_destination where id_cc_card=2157;
+----------------------+------------+-----------+
| destination | id_cc_card | id_cc_did |
+----------------------+------------+-----------+
| SIP/t564/12505551234 | 2157 | 1988 |
| SIP/testuser | 2157 | 1997 |
+----------------------+------------+-----------+
2 rows in set (0.00 sec)
To this table:
mysql> select did from cc_did where id=1988;
+------------+
| did |
+------------+
| 7805553315 |
+------------+
1 row in set (0.00 sec)
Except when I try to do that with a JOIN statement, I get this instead:
mysql> SELECT destination, id_cc_card, did FROM cc_did_destination as A join cc_did on A.id_cc_card=cc_did.iduser WHERE A.id_cc_card=2157;
+----------------------+------------+------------+
| destination | id_cc_card | did |
+----------------------+------------+------------+
| SIP/t564/12505551234 | 2157 | 6045553939 |
| SIP/testuser | 2157 | 6045553939 |
| SIP/t564/12505551234 | 2157 | 7805553315 |
| SIP/testuser | 2157 | 7805553315 |
+----------------------+------------+------------+
4 rows in set (0.01 sec)
Which makes even less sense!
UPDATE:
I found the problem, and it's all about matching the wrong fields. It should be:
WHERE cc_did.id=cc_did_destination.id_cc_did;
instead of:
WHERE cc_did.id=cc_did_destination.id_cc_card;
Best Answer
Here's a simpler way of looking at your queries
and
Since you want the record where
a.card = 2157
anda.card = b.id
then eitherwhere
statement so it is looking forb.id = 2157
ora.card = 2157
a.card = b.userid