What's wrong with this query?

Frizzell
Frizzell used Ask the Experts™
on
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;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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%';
HainKurtSr. System Analyst

Commented:
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

HainKurtSr. System Analyst

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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%';
HainKurtSr. System Analyst

Commented:
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%';
Sr. System Analyst
Commented:
'no rows selected' looks like not error, it is a message saying no such record found in your db...

Author

Commented:
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.
HainKurtSr. System Analyst

Commented:
use this

SELECT CUSTOMERS * from Upper(C_FIRST || ' ' || C_LAST) LIKE 'JANE DOE%';
Rajkumar GsSoftware Engineer

Commented:
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





Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial