CODEWITHSUNDEEP
mysqlsql
John Hoffman
John Hoffman

Reputation: 18607

Why am I getting a MySQL error?

Here is my query. Its intention is allow access to properties of the animals that constitute a match of two animals.

The match table contains columns for animal1ID and animal2ID to store which animals constitute the match.

SELECT id, 
    (SELECT * 
    FROM animals 
    WHERE animals.id=matches.animal1ID) AS animal1, 

    (SELECT * 
    FROM users 
    WHERE animals.id=matches.animalID) AS animal2 
FROM matches WHERE id=5

However, MySQl returns this error: Operand should contain 1 column(s).

Why? Is there an alternative way to do this, perhaps with a JOIN statement?

Upvotes: 0

Views: 46

Answers (2)

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

You should not use * in subqueries in SELECT clause. Rewrite your query in this way:

SELECT m.id, a1.*, a2.*
FROM matches as m
LEFT JOIN animals as a1
    on a1.id=m.animal1ID 
LEFT JOIN users as a2
    on a2.id=m.animalID
WHERE m.id=5

Or, just replace * with single column name. But, in this case you must be sure that your subquery will always return only one row.

Also you can use aliases to separate the fields in the way like this:

SELECT m.id, a1.name as an1_name, a2.name as a2_name, a1.some_field as blabla ...

And you can compare the results of your query prior to getting it in PHP, just add something like this in WHERE clause:

...
WHERE m.id=5 
    AND a1.name <> a2.name    -- or what you want

Upvotes: 3

Justin Pihony
Justin Pihony

Reputation: 67065

How about something more like this:

SELECT id, 
 animals.*, 
 users.* 
FROM matches 
    JOIN animals 
        ON animals.id=matches.animal1ID
    JOIN users 
        ON users.id=matches.animalID
WHERE id=5

If animals or users are not guaranteed, you can use a LEFT JOIN

SELECT id, 
 animals.*, 
 users.* 
FROM matches 
    LEFT JOIN animals 
        ON animals.id=matches.animal1ID
    LEFT JOIN users 
        ON users.id=matches.animalID
WHERE id=5

With these JOINS, you can pick whatever column you need from animals and users

SELECT id, 
 animals.animalName AS AnimalName, 
 users.animalName AS UserAnimalName
FROM matches 
    (LEFT) JOIN animals 
        ON animals.id=matches.animal1ID
    (LEFT) JOIN users 
        ON users.id=matches.animalID
WHERE id=5

Upvotes: 2

Related Questions