CODEWITHSUNDEEP
mysqlsql
Raphael Rafatpanah
Raphael Rafatpanah

Reputation: 19967

SQL to return one row with columns in another table, if a corresponding row exists in another table

I have two tables in a database, a & b.

Table b has a foreign key that references a column on a.

I'm trying to figure out how to get a single row from b with all of the columns from it's corresponding a. However, not all of b's rows will have corresponding rows in a.

The following SQL will return all rows in b that have corresponding rows in a with all the columns from both tables.

"SELECT * FROM b INNER JOIN a ON b.foo = a.foo

But I just want one row where b.foo equals some value with it's corresponding columns from a (if any).

So, naively:

"SELECT * FROM b INNER JOIN a ON b.foo = a.foo WHERE b.foo = 'some value'

The problem is, this sometimes returns no rows since there may not be a corresponding row in a. But we are guaranteed that some row in b exists where b.foo equals some value, and that should always be returned.

Is there a SQL command that can always return one row in b (given that the row in b exists) either with the corresponding columns in a or if a corresponding a does not exist, just the columns in b?

Upvotes: 1

Views: 588

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50970

Yes, if you change INNER JOIN to LEFT OUTER JOIN you will always receive the b row, and the a column values will be NULL if no matching row was found.

Upvotes: 4

Related Questions