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;
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;
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...
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%'
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%'
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%';
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%';
do you get any record from here
SELECT CUSTOMERS * from C_FIRST || ' ' || C_LAST LIKE 'JANE DOE%';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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%';
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
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
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.C_ID = ORDERS.O_ID
where CUSTOMERS.C_FIRST || ' ' || C_MI, C_LAST LIKE '%JANE DOE%';