CODEWITHSUNDEEP
mysqljoin
Cris McLaughlin
Cris McLaughlin

Reputation: 1211

MySQL complex join - Can't select multiple columns in second table

I can't figure out why I can't select multiple columns in the second table. Here are my tables:

Computers:
----------
id, cond, type

Images:
-------
id, foreignid, name, width, height, def

Here is my select statement:

SELECT
    id, cond, type,
    (SELECT name, width, height FROM images WHERE foreignid = computers.id ORDER BY def DESC LIMIT 1) AS image
FROM computers

This is the error I am getting:

Operand should contain 1 column(s)

Upvotes: 0

Views: 676

Answers (2)

Jacob Nelson
Jacob Nelson

Reputation: 3006

SELECT comp.id, comp.cond, comp.type, img.name, img.height, img.width FROM computers comp left join image img on img.foreignid = comp.id

Will return a computer, with its related image if that is what you are looking for.

Upvotes: 0

Hyperboreus
Hyperboreus

Reputation: 822

Are you trying to do something like this?

select c.id, c.cond, c.type, i.name, i.width, i.height from computers c
left join images i on i.foreignid=c.id
order by i.def desc limit 1

EDIT: But the join clause depends on what exactely you want. If you want all computers, have they images or not use

computers left join images

If you want all images, have they computers or not use

computers right join images

If you want only computers with images and images with computers use

computers inner join images

If you want all computers and all images use

computer outer join images

Upvotes: 2

Related Questions