Hi All, Here are queries, "select * from product WHERE LastName like concat(?, '%')" "Select concat(FirstName, ' ' , LastName) as FullName, Product_id from Customer join product where customerId = id" Above both queries working individually fine when I tried to combine these, I can't get any results. I want to combine the above two queries, where the user gives input "Full Name" and it should display the result. My second question is how to populate match results, I mean when users start typing it should display match results where users can select names. How I can accomplish these? I really appreciate your help. Note: I'm working with Groovy
I've no idea what Groovy is in this context, but SQL is SQL ... "Products" have a last name? Your tables have the first letter capitalised randomly or is there a reason customers has C and products has p? Try this select product.*, concat(Customer.FirstName,' ', Customer.LastName) as FullName from Customer left join product on Customer.id = product.customerId Code (markup): As for how to create an autocomplete box - that depends on what javascript libraries you're using, if any. =================== is this the Groovy you referred to?
My apology, it was typing mistake, I was just typing it from my head. I didn't clear it properly Table names are "Customer" and "Product" Customer Table Fields are "FirstName, LastName, CustomerId" Product Table fields are "PrdouctId, ProductName, PersonId (which is the same as CustomerId from Customer table) The below query work when the user enters the last name of the Customer. Instead of Lastname, customers should be able to enter first and last name in the input field. When customer enter the full name it should pull the data from both tables (Customer and Product). select Firstname, Lastname, CustomerId from Customer WHERE LastName like concat(?, '%')" Below query works when I run it, but there isn't any user input "Select concat(FirstName, ' ' , LastName) as FullName, ProductId from Customer join Product where CustomerId = PersonId; My problem is I wanna include this user input part (WHERE FullNmae like concat(?, '%'). This part is important because I wanna user to enter a name that we have in our database then it'll pull the data. To summarize, I want to combine the below query in one and I wanna have the condition as full name, whenever enter full name then it should display the results. How I can accomplish that. Select concat(FirstName, ' ' , LastName) as FullName, ProductId from Customer join Product where CustomerId = PersonId; WHERE FullNmae like concat(?, '%') That's the Groovy, which is a programming language, the groovy script is working fine because it works with a single table. I really appreciate your help, thank you
Update.... I used your query and added the added where clause, it works if I enter CustomerId or PersonId as input. How I can enter the full name as input? Is there a way to enter FullName as Input and it displays the results? I mean how can use FullName with where clause since this doesn't exist in a table? I tried but it didn't display any results. Below is the working query. "SELECT Product.*, concat(FirstName,' ', LastName) as FullName, CustomerId from Customer left join Product on PersonId = CustomerId WHERE CustomerId like concat(?, '%')"
You can use FullName by adding a having clause SELECT Product.*, concat(FirstName,' ', LastName) as FullName, CustomerId from Customer left join Product on PersonId = CustomerId having CustomerId like concat(?, '%') Code (markup): but people aren't searching on the CustomerId which is, presumably, an integer or one of those 32 char strings SELECT Product.*, CONCAT(FirstName,' ', LastName) AS FullName, CustomerId FROM Customer LEFT JOIN Product ON PersonId = CustomerId WHERE CONCAT(FirstName,' ', LastName) LIKE '%string%' OR ProductName LIKE '%string%' Code (markup):
Sarah, I can't thank you enough, for your help, you are great, the "HAVING" clause is working perfectly. Thank you so much