SQL query returning “Operand should contain 1 colu

问题:

I'm currently working on a query which will have all rows from one table, but only limited information from the other.

I've tried working with this query:

SELECT `t`.`uid`, `t`.`cid`, `t`.`id` FROM `tracking` as `t`
JOIN (SELECT DISTINCT(`p`.`id`, `p`.`firstname`, `p`.`lastname`, `p`.`company`) FROM `publishers` as `p`) as `p`
ON `p`.id = `t`.uid

However, I get the error as in the topic heading.

Can anyone see what I'm doing wrong here?

Edit:

Structure on tracking: id int(11) primary ai, cid int(11), uid int(11).

Structure on publishers: id int(11) primary ai, firstname varcher(60), lastname varchar(60), company varchar(100).

Sample: publisher id 1, firstname NIKOLAJ, lastname JEPSEN, company APECHO id 6, firstname ROBERT, lastname SCOTT, company FLAPJACK LTD

tracking id 1, cid 1, uid 1; id 2, cid 1, uid 6; id 3, cid 3, uid 1

回答1:

Surely no two publishers can share the same id, firstname, lastname, and company!!!

SELECT t.uid
     , t.cid
     , t.id 
  FROM tracking t
  JOIN publishers p
    ON p.id = t.uid;

回答2:

DISTINCT(p.id, p.firstname, p.lastname, p.company)

is the problem. Drop the parenthesis:

SELECT `t`.`uid`, `t`.`cid`, `t`.`id` FROM `tracking` as `t`
JOIN (SELECT DISTINCT `p`.`id`, `p`.`firstname`, `p`.`lastname`, `p`.`company` FROM `publishers` as `p`) as `p`
ON `p`.id = `t`.uid

That should allow the query to work... however, if you have to use distinct for this, there might be something else wrong with your data structure or query.