hi - looking at the results now, it seems you’re able to see 2 distinct UIDs that match for query 3 (without the A.uid filter) - isnt this what you were originally looking for? Or ar there more UID matches that are not showing up?
There are many more. As indicated earlier if you query for a unique ID in Table A (lets say limit 10 order asc) and then search for that uid in Table B, you’ll find the query returns a result --> This technically indicates the uid is in both tables A and B.
However, two problems occur after this point. #1 if you try and look for all uid that are similar between both tables, only 2 unique uids show up (there are many more) #2 out of the list of uids that returned in Table A, take the one that you found in Table B and query for it in Table A. Because it was listed in Table A it should also show up when you query for it in Table A, however, it does not.
Is it possible that there is a limit on the number of rows cheked when doing joins?
This is confusing for even me to undestand and i’m not sure what’s going on but here’s another example:
Say you have a white and black refrigerator that both have a touch screen to show what’s in the fridge.
You open the black fridge and see it contains bananas, apples, kiwis, watermelon, coconuts. Oddly enough, when you close the black fridge and look at the touch screen list and search for bananas, they are not there?? (this is problem #2 from above)
You open the white fridge and see it contains bananas, passion fruits, oranges, kiwis, clementines, and apples. Now you see that after opening the black and white fridge that the inside of both fridges have many fruits in common. However, when you close the doors and look at the touch screens on both fridges and look for common items, you see that there are only kiwis in both of them (referring to problem #1)
Very confusing, not really sure what’s going on but I appreciate everyone’s help. I’ll figure something out eventually.
Not sure I understand this
I understand there are 628224 distinct UIDs in A. I understand there are 712204 distinct UIDs in B from your count distinct earlier. So far, so good, but this in no way tells me how many matching IDs there are (yet).
You ran the inner join, and it returned 2 rows where the UIDs actually matched - again, nothing in the log indicates a failure or error.
I may be misinterpreting what you mean when you say ‘similar’. How have you established (outside of MapD) that there are actually more matches than 2?
Outside of Map D I have not, but without joining them directly you can visually see that there are more than just 2 uids in common.
List uids of Table A:
Find uid = 123 in Table B:
successfully found uid 123 in Table B
3. Find uid = 123 in Table A:
^ The above is a separate issue which we can ignore for now. But there real problem comes next:
4.Find matching uid’s in Table A and B:
Only shows these two but based on queries like 2 there are more uids in common but only 2 show when doing a query like 4
Thanks. You may have already done this, but have you checked if there are trailing spaces in the UID column?
ooo I have not. How would one check for that?
The following steps will confirm there are no trailing spaces etc.
If you could provide the output from
mapdql and the associated log of a complete issue showing a failed uid lookup likes
\version select uid from Live_Table where uid = 'PlaceBadUidHere'; select uid from bayesian_score where uid = 'PlaceBadUidHere'; select A.uid, B.uid from Live_Table A,bayesian_score B where A.uid = B.uid and a.uid = 'PlaceBadUidHere'; select A.uid, B.uid from Live_Table A, bayesian_score B where A.uid = B.uid;
It should help us move along our trying to help you.
Just to add a clarifying question, are you familar with the sematics of an inner join, in that it needs a matching record on both sides of the join for any records to be returned?
Your point 3 here is the crux of the problem. If we cant find 123 in Table A a join cannot find it either.