SQL Operand should contain 1 column(s)

Question

I think this should be quite simple but i just can't seem to figure it out.

I have 2 tables. Table A and table B are joined by a field which are the same value.

I want to select all rows from table A. From table B, I want to select all joined to table A where a column in table A is a specific value.

This is how I see it working

SELECT tableA.*, (SELECT * FROM tableB INNER JOIN tableA on tableB.id = tableA.id WHERE tableA.complete = 1) FROM tableA

I have run this code but i get this error

Operand should contain 1 column(s)

How can i get all values from table A and joining rows from table B where table A column is 1?

EDIT:

This is what I expect output should be

Table_A.someID    Table_A.col       Table_A.complete      Table_B.someID      Table_B.col
----------------|----------------|---------------------|-------------------|----------------
123             |   something    |          1          |    123            |   value
124             |   something    |          1          |    124            |   value
125             |   something    |          1          |    125            |   value
126             |   something    |          0          |                   |    
127             |   something    |          0          |                   |   

I want all rows from Table A regardless of column complete value and from Table B any row that links to Table A and complete is 1.

If you need anymore explanation i'll explain more

Answer 1

You dont need outer Query. This should suffice

SELECT tableA.* 
FROM tableB INNER JOIN tableA on tableB.id = tableA.id  
WHERE tableA.complete = 1

Answer 2

You just have the wrong syntax. It's actually simpler than what you're trying to do.

The following should work

SELECT a.* 
FROM tableA AS a
INNER JOIN tableB AS b
ON tableB.id = tableA.id
WHERE a.complete = 1

Alternatively, if you're looking for values from tableB, you can do the following:

SELECT a.*, b.*
FROM tableA AS a
INNER JOIN tableB AS b
ON tableB.id = tableA.id
WHERE a.complete = 1

Although the fact that both tableA and tableB have id columns may throw errors, depending. I would actually recommend you avoid a.* and b.* and rather explicitly list the columns you need, eg SELECT a.id, b.whatever, a.somethingelse FROM tableA AS a...

Specifically vis a vis not selecting * from your table, this is worth a read: Why is SELECT * considered harmful?

---

In light of your final edits, this is what you want:

SELECT a.someID, a.col, a. complete, b.someID, b.col
FROM tableA AS a
LEFT JOIN tableB AS b
ON tableB.id = tableA.id
WHERE a.complete = 1

You could do a., b. instead, but I wouldn't recommend that. Also note the left join. That's important.

Answer 3

left outer join can do your work :

SELECT *
FROM   tableA a
       LEFT OUTER JOIN tableB b
                    ON a.id = b.id
                       AND a.complete = 1 

it will return all the rows of tableA and those rows of tableB will be matched with tableA on the basis of id which have complete = 1 . if it doesn't match then simply NULL will be shown.

Answer 4

Edit: more detail in the question

SELECT * 
FROM TableA
LEFT OUTER JOIN TableB ON TableA.id = TableB.ID AND TableA.complete = 1

which will give you all the rows of TableA, and the matched data from TableB where complete is 1, and NULL otherwise