Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
Sorry, I'm not understanding what you're asking. You say "The data in the second query is old and should have been replaced with the data we see in the first query". So if the second query is old, and should be replaced...why does it matter that they're currently different? –  Derek Downey Feb 9 '12 at 21:03
    
The second query isn't old, the second query returns old data that no longer exists in the database. It's been replaced with the data that comes back when I do the first query. –  Ernie Feb 9 '12 at 22:06
    
A query cannot return data that don't exist in the database. The data is there. Data is not replaced or deleted by magic. We can't know what procedures, triggers you have and why you do not expect some rows not to be in your tables. –  ypercube Feb 9 '12 at 22:28
    
Oh, I completely agree. Which is why I'm baffled that my query is returning data that, well, isn't there. –  Ernie Feb 9 '12 at 22:35
1  
You do not put both conditions in the JOIN. What happens if you change your last query to : WHERE A.id_cc_card=2157 AND cc_did.id=1988 ? –  ypercube Feb 9 '12 at 23:27
show 4 more comments

migrated from serverfault.com Feb 9 '12 at 20:40

This question came from our site for professional system and network administrators.

1 Answer

Here's a simpler way of looking at your queries

select * 
from A
where A.did = 1988

-- returns record where card = 2157

and

select * 
from B join A on A.card = B.id
where B.userid = 2157 

Since you want the record where a.card = 2157 and a.card = b.id then either

  • Change your where statement so it is looking for b.id = 2157 or a.card = 2157
  • Or change your join so you're joining a.card = b.userid
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.