Can LIKE be used in a LEFT JOIN Statement ?

swatisonee

Hi,

I have the foll statement that is currently giving a "no records found " error although I have about 20 that match .

I have trouble with Joins and I suspect its the way this has been formed thats throwing up the error.

Issues to contend with :

a. The Customerid field in VisitReport is a varchar one ( it has data like 200,123,56 or 1,45 or just ,,, if the user has preferred to leave the field blank)

b. As a result, i cannot use the "=" to work while matching the customerids of the 2 tables.

c. The purpose of this sql is to list all records in VisitReport where atleast 1 customerid will match a customerid in CustomerList

d. Example : If ABC Corp, having customerid 1 in the CustomerList table appears in a record of VisitReport which has customerid 1,67,209 , then ABC Corp should get listed as having matched the criterion of appearing in Visit Report.

e. Would greatly appreciate advise on how I could rectify the sql below.

Thanks: Swati

$sqlb = "SELECT `Customers_List`.* FROM `Customers_List`
            LEFT JOIN `VisitReport` ON `Customers_List`.Customerid LIKE `VisitReport`.Customerid
            WHERE  `Visit_Report`.Product LIKE '$name%'";

bogu

What is your tables structure?

swatisonee

Customerid in Customers List is an INT(11) field, Company , Address,City and Zipcodes are Varchar(255).

Customerid in Visit Report is Varchar (50) as is Company and Products. Date is a date field, and report is a LongText field .

stewartship

ON a quick glance in your query you are comparing the two customer id's from the tables, but because on one table the customer id is an INT and on the other it is a VARCHAR i think that they will not match, they need to be of the same Type in order to be compared.

swatisonee

Oh youre saying that if i change the type from INT to VARCHAR in the CustomersList Table i should be able to get a match ?

swatisonee

Oh i cannot change customerid in CustomersList from INT(11) . its an autoincrement field and i dont think i should mess with it. Can anyone give me some other suggestions pl ?

Thanks.Swati