Help with Join in Mysql

Asked

Viewed 68 times

0

I found several questions/answers about John on the site, but I still can’t create the command I need. What I need is the following:

I have the following tables:

item (I have two entries)

id
descricao
---------------
1 - dipirona
2 - paracetamol

itemdetalhe (of these items, I only have dipirona of two different brands)

id
item
marca
-------------------
1 - 1 - medley
2 - 1 - medlab

itematual (in stock, I have 1000 dipironas of one brand and 1500 of other)

id
itemdetalhe
qtd
----------------
1 - 1 - 1000
2 - 2 - 1500

What I need is to generate something like this:

item.id | itemdetalhe.id | item.descricao | itemdetalhe.marca | itematual.qtd
1         1                dipirona         medley              1000
1         2                dipirona         medlab              1500
2                          paracetamol
mysql sql database query select

  • takes a look at the site that has several questions with answers about join in the mysql

  • @Ricardopunctual I have seen some yes answers, including my questions, however, are not helping me in this specific case

  • but explain better, what the difficulty then?

  • @Ricardopunctual the difficulty is that I could not do what I need in the question. gives a read

  • but where is your sql? for me remains vacant

  • 2

    What you want is the left join. So you can search the data with a base table ignoring the lack of them in another table

  • that’s right @Sorack, I tested it here and solved it. if it is possible to write the answer, feel free to earn your points. hug

Show 2 more comments

1 answer

1


In case you need to make one JOIN that brings the information from the base table even if there is no data in the reference tables. In this other answer you can check the differences between the JOINS. Applying to your need:

SELECT i.id AS 'item_id',
      id.id AS 'item_detalhe_id',
      i.descricao,
      id.marca,
      ia.qtd
  FROM item i
      LEFT JOIN itemdetalhe id ON id.item = i.id
      LEFT JOIN itematual ia ON ia.itemdetalhe = id.id

Where item data will appear even if there is no data in tables itemdetalhe and itematual.

Browser other questions tagged mysql sql database query select

You are not signed in. Login or sign up in order to post.