New-To-SQL New-To-SQL - 1 year ago 110
MySQL Question

MYSQL Inner join results is empty

I need to join 2 tables based on a customer ID.

My tables are Table1 = 'vbq-customers' and Table2 = 'vbq-sales'

The columns of Table1 are : Customer_id | First_name | last_name etc..
The columns of Table2 are : Receipt_nb | receipt_date | receipt_customer_code etc..

I want to join on Customer_id = receipt_customer_code

The result of this query is empty despite the fact that if have checked the presence of similar info to be joined.


DROP TABLE `vbq-sales-customers`;
CREATE TABLE `vbq-sales-customers`

FROM `vbq-sales`
INNER JOIN `vbq-customers`
ON `vbq-sales`.receipt_customer_code=`vbq-customers`.Customer_id



Can anyone help me figure out what I am missing here?

Answer Source

A couple of possibilities. Easiest to explain would be that one (or both) of the tables are empty, contain zero rows.

Or, there are no rows that "match". There aren't any rows in vbq-sales that have a value in receipt_customer_code table that is equal to a value in the Customer_id column of vbq-customers.

We don't see the datatype of either of those columns. There could an implicit data conversion going on , or we might have fixed length CHAR columns of different lengths (which won't be equal.) (Doh! Please excuse my Oracle.)

Without table definitions, we're just guessing.

I strongly recommend you test just the query (i.e. just the plain SELECT statement), before putting it into a CREATE TABLE AS statement. You don't indicate there's any error creating the table, so we'll disregard other reasons the CREATE TABLE might fail.

I don't know what's up with the BEGIN and END. That made it look like this was an anonymous block in Oracle. But the backticks around the identifiers are very MySQL. It's confuzzling collection of syntax there.

If the problem is that the SELECT query is returning zero rows, just debug that. Get rid of all of the CREATE TABLE AS and everything else.

To inspect the actual contents stored in the columns, you can use the HEX() function... then everything, including non-printable characters will be printable characters 0-9A-F, which you can look at more closely.

I suspect you may just have some unprintable characters in one of the values, e.g. tab x'09', newline x'0A', carriage return x'0D', etc.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download