Sql query to find select customer that bought in the specific company address

Question

Here is my table

CREATE TABLE Customer 
(
     ID CHAR(50),
     Customer_FName CHAR(50),
     Customer_Lname CHAR(50)
);

CREATE TABLE Buying 
(
     Customer_ID CHAR(50),
     Product_ID CHAR(50),
     Order_Time CHAR(50)
);

CREATE TABLE Product 
(
    ID CHAR(50),
    Name CHAR(50),
    Address CHAR(50)
);

I am trying to find all customers who bought a product with their company's address in 'Burwood' and list the customer's ID, names, product ID, product name and product address

Select 
    Buying.Customer_ID, Buying.Product_ID, Product.ID, 
    Product.Name, Customer.ID, 
    Customer.Customer_FName, Customer.Customer_Lname
from
    ((Buying
inner join 
    Product on Buying.Product_ID = Product.ID)
inner join 
    Customer on Buying.Customer_ID = Customer.ID)
where
    Product.Address like '%Burwood%';

I want to combine three table but It shows 'no rows selected'.

I also give a sample data table

To start with use **lower(Product.Address) LIKE '%burwood%'**. If still not working, share some sample data — Gro, Aug 28 '19 at 00:50

Gro · Accepted Answer · 2019-08-28T04:33:52.907

Any reason why you have chosen CHAR as the datatype for all columns of all tables? For CHAR based columns, DBs tend to pad the values up to the column width defined. That said, it is not why you are not getting result. You may want to check if during insert you are adding any extra space or non-printable characters in IDs resulting into failed inner joins.

I suggest change the fields to VARCHAR instead, validate your inserts and then query just as I demonstrated below. You will start getting result, as I am..

CREATE TABLE Customer (
    ID varchar(50),
    Customer_FName varchar(50),
    Customer_Lname varchar(50)
);

CREATE TABLE Buying (
    Customer_ID varchar(50),
    Product_ID varchar(50),
    Order_Time varchar(50)
);

CREATE TABLE Product (
    ID varchar(50),
    Name varchar(50),
    Address varchar(50)
);


insert into customer values('10001', 'John', 'Smith');
insert into Buying values('10001', '772', '2016/09/01');
insert into Product values('772', 'Telephone', '22 Ave, Burwood');

select b.product_id, p.name, b.customer_id, c.customer_fname, c.customer_lname
from buying b
join product p on b.product_id = p.id
join customer c on b.customer_id = c.id
where lower(p.address) like '%burwood%'

i'm so sorry because i input old sql query for my table – DRGN Aug 28 '19 at 04:31 — DRGN, Aug 28 '19 at 04:31

Neeraj Agarwal · Answer 2 · 2019-08-28T01:58:15.583

1

Please try this, it works for me:

Select B.Customer_ID, B.Product_ID, P.ID, P.Name, C.ID, C.Customer_FName, C.Customer_Lname
from Buying B
INNER JOIN Product P ON B.Product_ID = P.ID
INNER JOIN Customer C ON B.Customer_ID = C.ID
WHERE P.Address LIKE '%Burwood%'

edited Aug 28 '19 at 01:58

answered Aug 28 '19 at 01:48

Neeraj Agarwal

1,059
6
5

score -1 · Answer 3 · answered Aug 28 '19 at 00:50

-1

Try this:

SELECT C.*,B.*, P.*
FROM Customer C,Buying B, Product P
WHERE B.Product_ID=P.ID
AND B.Customer_ID=C.ID
AND P.Address LIKE '%Burwood%';

answered Aug 28 '19 at 00:50

ChrisFNZ

597
1
4
21

Please don't perpetuate this comma syntax, which was replaced over 20 years ago with the modern join syntax. – TomC Aug 28 '19 at 04:13
It may be valid, however it is not considered ideal, and as SO is to help people do things the best way, a syntax that was superseded in 1992 (over 25 years ago) should be consigned to history. I suspect the only reason its still supported by any modern database is for backward compatibility. – TomC Aug 29 '19 at 02:08
It doesn't make an iota of differece for Inner joins and reduces query verbosity. https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – ChrisFNZ Aug 29 '19 at 02:20

Sql query to find select customer that bought in the specific company address

3 Answers3