CODEWITHSUNDEEP
mysql
fishbaitfood
fishbaitfood

Reputation: 659

JOIN every record from A with specific B.id from B where B.id =?

I have two tables, A and B.

Fields of A: id, name

-----------------
 ID |   NAME    |
-----------------
 1  |   name1   |
-----------------
 2  |   name2   |
-----------------
 3  |   name3   |
-----------------
 4  |   name4   |
-----------------
 5  |   name5   |
-----------------

Fields of B: bid, id

-----------------
 BID    |   ID  |
-----------------
 11     |   1   |
-----------------
 11     |   2   |
-----------------
 12     |   1   |
-----------------
 12     |   2   |
-----------------
 12     |   3   |
-----------------

I want to perform a query to show ALL records from A with columns A.id, A.name on the left. And join a third column from B ON id WHERE bid = '11'.

So I would have something like this:

--------------------------------
 A.ID   |   A.NAME  |   B.BID
--------------------------------
 1      |   name1   |   11
--------------------------------
 2      |   name2   |   11
--------------------------------
 3      |   name3   |
--------------------------------
 4      |   name4   |
--------------------------------
 5      |   name5   |
--------------------------------

Any suggestions?

Upvotes: 0

Views: 458

Answers (3)

Taryn
Taryn

Reputation: 247650

You will want to use a LEFT JOIN

SELECT A.ID, A.NAME, B.BID
FROM A
LEFT JOIN B
   ON A.id = B.id
WHERE B.BID = yourValue -- use the WHERE clause for additional filtering

See SQL Fiddle With Demo

Based on your update, you will want to move the filter from the WHERE clause to the JOIN:

SELECT A.ID, A.NAME, IFNULL(B.BID, '') BID
FROM A
LEFT JOIN B
   ON A.id = B.id
  and b.bid = 11

See SQL Fiddle with Demo

Upvotes: 3

juergen d
juergen d

Reputation: 204746

List all values no matter bid has a value with left join:

select a.id, a.name, b.bid
from a
left join b on b.id = a.id
where b.bid = 11

Use inner join if bid has a specific value:

select a.id, a.name, b.bid
from a
inner join b on b.id = a.id
where b.bid = 11

Good explanation of joins

SQLFiddle example

Upvotes: 2

Blaise Swanwick
Blaise Swanwick

Reputation: 1755

Do you have a column in table_B that contains the same value as a.id in table_A? If not, you will not be able to join table_b to table_a. What you need here is

WHERE bid = ?

Should be something like

WHERE bid = a.bid

OR

WHERE a.ID = b.AID

Upvotes: 1

Related Questions