CODEWITHSUNDEEP
mysqljoinleft-join
H. Ferrence
H. Ferrence

Reputation: 8116

How to join 2 MySQL Tables without returning all joined rows

I have 2 tables that I want to join and all I want to know from the joined table is whether there is an exiting row that matches the join condition. I do not want to return all rows the the joined table.

Example:

Table A
ID Name
1  Bob
2  Sally
3  Joe

Table B
Num ID Data
1   1  anything
2   1  some more
3   3  thats it

What I want it to return all rows from table A but all I care to know is whether there is a matching ID on table B. I do not want to return all matching rows.

So my output would look something like this:

ID Name  Has Data
1  Bob   Yes
2  Sally No
3  Joe   Yes

I do not want the output to look like this (which it currently does):

ID Name  Has Data
1  Bob   Yes
1  Bob   Yes
2  Sally No
3  Joe   Yes

Here is my query:

  SELECT a.ID, Name
  FROM table_a AS a
  LEFT JOIN (SELECT DISTINCT b.ID FROM table_b) AS b ON b.ID = a.ID

I get this error message: Unknown column 'b.ID' in 'field list'

Upvotes: 0

Views: 37

Answers (1)

McNets
McNets

Reputation: 10827

SELECT a.ID, Name
CASE WHEN b.ID IS NULL THEN 'No' ELSE 'Yes' END as [Has data]
FROM table_a AS a
LEFT JOIN table_b ON b.ID = a.ID
GROUP BY a.Id, Name

SELECT a.ID, Name
CASE WHEN EXISTS (SELECT 1
                  FROM table_b b
                  WHERE b.ID = a.Id) THEN 'Yes' 
     ELSE 'No' END as [Has data]
FROM table_a AS a

If LEFT JOIN doesn't returns rows, b.ID will be null, then use a CASE statement to return yes or no depending on it value.

Upvotes: 1

Related Questions