Question 318295 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql: Select statement in one query to retrieve all data from one table and just one from another table

I want to select all columns from table A and just one column from table B, the information is related so I tried to join tables, but didn’t work.

The following setup can be used as example, note that genreId is a foreign key to tbGenres(id):

tbBooks

id title genreId
1 ‘title1’ 4
2 ‘title2’ 4
3 ‘title3’ 2

tbGenres

id name
1 ‘Action’
4 ‘Comedy’

I want a query that gives me the following, based on the genre_id. So for example, when we pass 4 as param of the query:

id title genreId name
1 ‘title1’ 4 ‘Comedy’
2 ‘title2’ 4 ‘Comedy’

I tried to use INNER JOIN and other JOINS with a WHERE clause, like this:

SELECT a.*, b.name from tbBooks AS a INNER JOIN tbGenres AS b WHERE a.genreId = 4

But it returned multiple repeated rows with incorrects tbGenres names.

Can someone help with this one? I also thought of using UNION but I can’t since I don’t have the same number of columns in both tables.

Tags:

2

Answers


  1. 0

    You can use the INNER JOIN clause with the correct ON condition. . Here is the corrected code:

    SELECT a.*, b.name
FROM tbBooks AS a
INNER JOIN tbGenres AS b ON a.genreId = b.id
WHERE a.genreId = 4;

    Here is the fiddle link

    Login or Signup to reply.
  2. 0

    Some comments on your question

    ‘INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product’ -https://dev.mysql.com/doc/refman/8.0/en/join.html

    your query segment

    SELECT a.*, b.name from tbBooks AS a INNER JOIN tbGenres AS b ;

    results in

    +------+--------+---------+--------+
| id   | title  | genreId | name   |
+------+--------+---------+--------+
|    1 | title1 |       4 | Action |
|    1 | title1 |       4 | Comedy |
|    2 | title2 |       4 | Action |
|    2 | title2 |       4 | Comedy |
|    3 | title3 |       2 | Action |
|    3 | title3 |       2 | Comedy |
+------+--------+---------+--------+
6 rows in set (0.001 sec)

    with the condition

    SELECT a.*, b.name from tbBooks AS a INNER JOIN tbGenres AS b WHERE a.genreId = 4;

    results in

    +------+--------+---------+--------+
| id   | title  | genreId | name   |
+------+--------+---------+--------+
|    1 | title1 |       4 | Action |
|    1 | title1 |       4 | Comedy |
|    2 | title2 |       4 | Action |
|    2 | title2 |       4 | Comedy |
+------+--------+---------+--------+
4 rows in set (0.001 sec)

    Changing the condition to

    SELECT a.*, b.name from tbBooks AS a INNER JOIN tbGenres AS b WHERE a.genreId = 4 and (a.genreid = b.id);

+------+--------+---------+--------+
| id   | title  | genreId | name   |
+------+--------+---------+--------+
|    1 | title1 |       4 | Comedy |
|    2 | title2 |       4 | Comedy |
+------+--------+---------+--------+
2 rows in set (0.001 sec)

    but the better code is supplied in the answer from @User12345

    a UNION is a a vertical join and not the result you want AND you are wrong to think you cannot union tables with different number of columns, you can supply dummy values if need be. You also should name columns rather than use *

    select a.* from tbBooks a where genreid = 4
union
select b.ID,b.name,'' from tbGenres b where id = 4;

+------+--------+---------+
| id   | title  | genreId |
+------+--------+---------+
|    1 | title1 | 4       |
|    2 | title2 | 4       |
|    4 | Comedy |         |
+------+--------+---------+
3 rows in set (0.001 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search