MYSQL how to query in 2 tables where the query may or may not exist in one of the tables?

Asked

Viewed 26 times

-1

Good people!! I’m new around here, I need your help.

I need to create a table/query where the records made by the user returns. This user may or may not be part of a working group, where several users share their records.

a) I need the records made by the user to be listed even though he has not linked any working group;

b) In addition, I need the records of this user that were linked to any work group to be listed in the same table/query;

c) And, if there are records of other users belonging to the same junk group, be listed.

Ex.:

Product | User | Group | Company

Caixa1 | 1 | 22 | 589
Caixa2 | 3 | 22 | 589
Caixa3 | 1 | NULL | 589

In the example the 3 results are true.

Tables:

  1. bemtbgeral = tb1;
  2. grouposusercentral = tb2;

To tb1 is my main table, in it I need to check if the user has access to the record that he created (caduser), then I need to bring also the records of other users belonging to the same work group (idgroup). To tb2 are the information of each working group and user.

My code:

// caduser -> é a coluna com a id do usuário
// idesc -> é a coluna com a id da empresa do usuário
// idgrupo -> é a coluna com a id do grupo de trabalho que o usuário faz parte

"SELECT * FROM bemtbgeral AS tb1
WHERE tb1.caduser = '".$_SESSION['iduser']."'
OR EXISTS(SELECT * FROM gruposusercentral AS tb2
WHERE tb2.caduser = '".$_SESSION['iduser']."'
AND tb2.idesc = '".$_SESSION['idempresa']."'
AND tb2.idgrupo = tb1.idgrupo)";

In this query he returns me the first true condition, but I need you to bring me the two conditions if there are any records that he created and other members of the same group.

mysql mysqli

  • Important you [Dit] your question and explain objectively and punctually the difficulty found, accompanied by a [mcve] of the problem and attempt to solve. To better enjoy the site, understand and avoid closures and negativities worth reading the Stack Overflow Survival Guide in English.

  • I do not understand why I closed my question... I have already edited and added more details.

  • There are some links I passed in the previous comment that explain. If you still haven’t really understood how to edit for reconsideration, you can address the subject in [meta] with the "question-specific" tag (and lilnk for the post)

1 answer

0


If understood correctly use a junction.

SELECT * FROM bemtbgeral AS tb1 LEFT OUTER JOIN gruposusercentral AS tb2 ON (tb2.idgrupo = tb1.idgrupo)
WHERE tb1.caduser = '".$_SESSION['iduser']."'
OR tb2.caduser = '".$_SESSION['iduser']."'
AND tb2.idesc = '".$_SESSION['idempresa']."'

  • I appreciate your help. I am testing the indicated solution, at first the records that do not have the "idgroup" are returning as it should, but the records that have the "idgroup" returns only the last record, replicating this last record by the total amount of results found.

  • It worked well friend, I added only "GROUP BY tb1.id". Thank you very much for the great help!

Browser other questions tagged mysql mysqli

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