Link to home
Start Free TrialLog in
Avatar of Frizzell
Frizzell

asked on

What's wrong with this query?

Running a query on two different tables and trying to get info on one particular person however, my query is not running.  What's wrong with the query??

SELECT CUSTOMERS.C_FIRST, C_MI, C_LAST, ORDERS.O_DATE, O_METHPMT
WHERE CUSTOMERS.C_FIRST, C_MI, C_LAST, LIKE JANE DOE%'
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.C_ID = ORDERS.O_ID;
Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image

SELECT CUSTOMERS.C_FIRST, C_MI, C_LAST, ORDERS.O_DATE, O_METHPMT
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.C_ID = ORDERS.O_ID
where CUSTOMERS.C_FIRST || ' ' ||  C_MI, C_LAST LIKE '%JANE DOE%';
what is wrong? actually you should ask what is right :)

here is a sample, but I am not sure it is what you want to get, just check the syntax...
SELECT   CUSTOMERS.C_FIRST,
         C_MI,
         C_LAST,
         ORDERS.O_DATE,
         O_METHPMT
  FROM      CUSTOMERS
         INNER JOIN
            ORDERS
         ON CUSTOMERS.C_ID = ORDERS.O_ID
 WHERE   C_LAST LIKE 'JANE DOE%'

Open in new window

I guess I got it...
SELECT   CUSTOMERS.C_FIRST,
         C_MI,
         C_LAST,
         ORDERS.O_DATE,
         O_METHPMT
  FROM      CUSTOMERS
         INNER JOIN
            ORDERS
         ON CUSTOMERS.C_ID = ORDERS.O_ID
 WHERE   CUSTOMERS.C_FIRST || ' ' || C_LAST LIKE 'JANE DOE%'

Open in new window

Avatar of Frizzell
Frizzell

ASKER

I truncated it and I am getting the erro 'no rows selected'.
SELECT CUSTOMERS.C_FIRST, C_MI, C_LAST, ORDERS.O_DATE, O_METHPMT
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.C_ID = ORDERS.O_ID WHERE CUSTOMERS.C_FIRST || ' ' || C_LAST LIKE 'JANE DOE%';
no rows selected means your records name lastname is not like 'JANE...'

do you get any record from here

SELECT CUSTOMERS * from C_FIRST || ' ' || C_LAST LIKE 'JANE DOE%';
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I found the row with your original query but it only finds the first name and is case sensitive i.e. John but wouldn't find John Doe.  Strange but I am glad for your assistance.
use this

SELECT CUSTOMERS * from Upper(C_FIRST || ' ' || C_LAST) LIKE 'JANE DOE%';
Hi,

Read this to know about the errors  in your query in detail

I am experienced in SQL Server than Oracle. But If I am right, the basic syntax of SQL is common for both.


Your query is not correct. There are MANY errors that I will try to explain you.


Basic format of an select query is

SELECT  FROM  WHERE

For eg:- SELECT * FROM EMPLOYEES WHERE EMPLOYEE_NAME = 'Frizzell'

But in your query it looks like
SELECT * WHERE EMPLOYEE_NAME = 'Raj' FROM EMPLOYEES;

'WHERE' condition should come only next to 'FROM'



If we are using INNER JOIN to connect two tables, there will be result ONLY when there are matched records in all the tables connected by INNER JOIN. In your case you need to verify whether there are records matching your search criteria in CUSTOMERS & ORDERS table.

To check for matched records in CUSTOMERS table run this query

SELECT * FROM CUSTOMERS
WHERE C_FIRST like '%JANE DOE%' OR
C_LAST like '%JANE DOE%' OR
C_LAST + ' ' + C_FIRST  like '%JANE DOE%' OR
C_FIRST + ' ' + C_LAST  like '%JANE DOE%';

(I am not sure whether in Oracle, || is used to append fields. In SQL Server, I use +. So if this query not works, replace it.)

If this query have result, then there is related records in CUSTOMERS table in C_FIRST field or C_LAST field OR C_FIRST & C_LAST fields when concatinated.

If this query have result, then note down the CustomerID of this Customer from the result. For eg, assume C_ID = 1.
Then go for next query to check whether this customer have related fields in ORDERS table

SELECT * FROM ORDERS WHERE C_ID = 1;

Here give C_ID value which you got from first query. If this query also have result, we can confirm that there are result in both tables for the customer 'JANE DOE'

Also another error that I noticed in your query is that you have used INNER JOIN for the tables CUSTOMERS & ORDERS like
CUSTOMERS.C_ID = ORDERS.O_ID

This is wrong logically. You need to join using the CustomerID field (C_ID) which is present in both tables, which is the foreign key from ORDERS table to CUSTOMERS table.
so CUSTOMERS.C_ID = ORDERS.C_ID is logically right.


Another error is that you specified in WHERE condition!
WHERE condition's syntax is  = . But in your query fields like CUSTOMERS.C_FIRST, C_MI & C_LAST have no  part.

Another error is in the part after LIKE.
LIKE '%JANE DOE%' is right. You need to use single quotes & ampersand (%) on both sides of the search string.



So the correct & optimized query to search for a customer based on his/her first & last name is

SELECT CUSTOMERS.C_FIRST, C_MI, C_LAST, ORDERS.O_DATE, O_METHPMT
FROM CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.C_ID = ORDERS.C_ID
WHERE C_FIRST like '%JANE DOE%' OR
C_LAST like '%JANE DOE%' OR
C_LAST + ' ' + C_FIRST  like '%JANE DOE%' OR
C_FIRST + ' ' + C_LAST  like '%JANE DOE%';


Hope this helps

--Raj