1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How combine these queries

Discussion in 'MySQL' started by Rozard Dard, Jun 22, 2020.

0
  1. #1
    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
     
    Solved! View solution.
    Rozard Dard, Jun 22, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    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?

    upload_2020-6-23_15-38-16.png
     
    sarahk, Jun 22, 2020 IP
  3. Rozard Dard

    Rozard Dard Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    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
     
    Rozard Dard, Jun 23, 2020 IP
  4. Rozard Dard

    Rozard Dard Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #4
    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(?, '%')"
     
    Rozard Dard, Jun 23, 2020 IP
  5. #5
    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):
     
    sarahk, Jun 23, 2020 IP
  6. Rozard Dard

    Rozard Dard Peon

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #6
    Sarah, I can't thank you enough, for your help, you are great, the "HAVING" clause is working perfectly. Thank you so much
     
    Rozard Dard, Jun 24, 2020 IP