CODEWITHSUNDEEP
mysqlsqloracle-databasesqlplus
user3488037
user3488037

Reputation: 1

Unknown command error - SQL+/Oracle

I am doing a query for a class where I need to select pet owner names based on whether or not they own a Chihuahua.

I have to use two tables, PetOwner and Pet. PetOwner has an owner number (ownerNo), owner name (oLName, oFName), and Pet has owner number and petType. Here is the code I am trying to use:

SELECT 
    PetOwner.oFName, 
    PetOwner.oLName
FROM 
    PetOwner, 
    Pet
WHERE 
    PetOwner.ownerNo = (SELECT 
                            Pet.ownerNumber 
                        FROM 
                            Pet 
                        WHERE 
                            Pet.petType = 'Chihuahua'
                        );

The error says "unknown command beginning "Pet.petTyp..." - rest of line ignored." I know the issue is in the WHERE clause, but I can't seem to tweak it to where it will work, and I am sure the answer is obvious.

I just need a second set of eyes to look it over.

How can i do this ?

Upvotes: 0

Views: 1984

Answers (4)

SpiderCode
SpiderCode

Reputation: 10122

Your query is confusing sql for which Pet to select parent one or child one. Provide alias for child table in your sub query as mentioned below :

SELECT 
    PetOwner.oFName, 
    PetOwner.oLName
FROM 
    PetOwner, 
    Pet
WHERE 
    PetOwner.ownerNo = (SELECT 
                            p.ownerNumber 
                        FROM 
                            Pet p
                        WHERE 
                            p.petType = 'Chihuahua'
                        );

Upvotes: 0

Mayuri
Mayuri

Reputation: 412

 SELECT oFName, oLName
 FROM PetOwner
 WHERE ownerNo = (SELECT ownerNumber FROM Pet WHERE petType = 'Chihuahua');

Upvotes: 0

Mighty
Mighty

Reputation: 121

The following query worked for me:

SELECT ownername
FROM PetOwner
WHERE PetOwner.ownerNo = (SELECT Pet.ownerNo FROM Pet WHERE Pet.petType = 'Chihuahua');

Regards.

Upvotes: 0

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

Inside your query:

SELECT PetOwner.oFName, PetOwner.oLName
FROM PetOwner, Pet

You have mentioned the table 'Pet' here. Table 'Pet' shouldn't be mentioned here, as you're selecting the oFName and oLName from the table 'PetOwner', not 'Pet'.

A simple join query will give you exactly what you want. No need to use subquery in the above mentioned way. You need to join Pet table with PetOwner table, based on PetOwner.ownerNo = Pet.ownerNumber, under the condition that Pet.petType is 'Chihuahua'.

So the completed query is:

SELECT po.oFname, po.oLName
FROM PetOwner as po
JOIN Pet as p
ON po.ownerNo = p.ownerNumber
WHERE p.petType = 'Chihuahua'

Upvotes: 1

Related Questions