-1

I am simply querying a table

select label,  proces,  product from Signalering sig

What I want to add to this query is the chance to JOIN the result of the Select with 3 different tables. Let's say the case of joining with 1 table only. The query would look like:

select label,  proces,  product from Signalering sig
JOIN ScreeningLabelAuthorizationLock p
ON sig.label = p.Value

Now I want to join in the same way, at the same time, the result of the select with 2 more table. In my mind it would look like this:

select label,  proces,  product from Signalering sig
JOIN ScreeningLabelAuthorizationLock p
ON sig.label = p.Value
JOIN ScreeningProcessAuthorizationLock q
ON sig.proces = q.Value
JOIN ScreeningProducthAuthorizationLock s
ON sig.proces = s.Value

The result is not the expected one because the join works on the result of what comes before it, while I want it to be working from the Select statement. Thanks in advance!

EDIT: the correct result is given by the following query:

select [signaleringid], label,  proces,  product from Signalering sig
JOIN ScreeningLabelAuthorizationLock p
ON sig.label = p.Value
union
select [signaleringid], label,  proces,  product from Signalering sig
INNER JOIN ScreeningProcessAuthorizationLock q
ON sig.proces = q.Value
union
select [signaleringid], label,  proces,  product from Signalering sig
INNER JOIN ScreeningProductAuthorizationLock r
ON sig.product = r.Value

What I am looking is a way to avoid 3 Select queries and make it be only 1.

Tarta
  • 1,729
  • 1
  • 29
  • 63
  • 1
    Sample data and expected results would be helpful here. I suspect you might want to be using an `outer join` instead... – sgeddes Jun 12 '18 at 21:56
  • 1
    With MySQL default JOIN is an inner Join. Do you want a left join perhaps? https://stackoverflow.com/questions/4418776/what-is-the-default-mysql-join-behaviour-inner-or-outer https://www.w3schools.com/sql/sql_join.asp – JGFMK Jun 12 '18 at 22:08
  • @sgeddes just updated with an example! – Tarta Jun 12 '18 at 22:49
  • You still have not clearly described how output is a function of input, and it is not clear what such a specification your example is an example of. Give a rule about when a row appears in the result based on what parts of it appear and/or don't appear in input tables. – philipxy Jun 13 '18 at 05:35

2 Answers2

0
select label,  proces,  product from Signalering sig
LEFT JOIN ScreeningLabelAuthorizationLock p
ON sig.label = p.Value
LEFT JOIN ScreeningProcessAuthorizationLock q
ON sig.proces = q.Value
LEFT JOIN ScreeningProducthAuthorizationLock s
ON sig.proces = s.Value
olleo
  • 378
  • 3
  • 14
0

Given your sample data and expected results, I believe you want to be using union all:

select s2.*
from Signalering s 
    join ScreeningLabelAuthorizationLock s2 on s.label = s2.value
union all
select s2.*
from Signalering s 
    join ScreeningProcessAuthorizationLock s2 on s.proces = s2.value
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • this one workks, but, is there a way to keep only one Select instead of repeating it all the time? – Tarta Jun 13 '18 at 08:45