How to take the join method of the custom variable?

The task is to change the type of join depending on some conditions.
Set the variable according to some condition:
set @type = (SELECT CASE WHEN (subquery) = 0 
THEN 'left' 
ELSE 'inner' 
END)


Then use the variable.
@type JOIN table ON ...
April 19th 20 at 12:08
2 answers
April 19th 20 at 12:10
Solution
I always do a left join
and in the where clause to write an inner join
select a.*, b.*
from table1 a
 left join table2 b on (a.id = b.id) 
where 1=1 /*constant*/
and b.id is not null /*if this line is the inner join to left join*/
It would be too easy :)
In this case, break condition and the result does not correspond to the expectations :( - Marietta77 commented on April 19th 20 at 12:13
@Marietta77, Give an example of broken logic, or I don't understand. All must comply with, according to postavlennoy challenge =) - Althea24 commented on April 19th 20 at 12:16
@Althea24, there is quite a complex query, and tables a few pieces — i.e., write it completely useless.
Piss in the fact that in one case in table2 contains rows with table2.id, table2.*** (from your example), but not in others.
Well this specifics from the different customers and to change it.

And it turns out that the INNER one customer will not receive their data at all.
And when LEFT, the second client will receive too much data, it will interfere and it will be unnecessary to swear.
Damn, here in the opinion of the deal... however not likely. - Marietta77 commented on April 19th 20 at 12:19
@Marietta77, Here is an example, show on someone who was
with table1 as(
select 1 id, 'table1_1' text1 union all
select 2,'table1_2' union all
select 3,'table1_3' union all
select 4,'table1_4' union all
select 5,'table1_5'
)
,table2 as (
select 1 id2, 1 tab1_id,'table2_1' text2 union all
select 2, 1 tab1_id,'table2_2' union all
select 3, 1 tab1_id,'table2_3' union all
select 4, 2 tab1_id,'table2_4' union all
select 5, 2 tab1_id,'table2_5' union all
select 6, 2 tab1_id,'table2_6' union all
select 7, 3 tab1_id,'table2_7' union all
select 8, 3 tab1_id,'table2_8' union all
select 9, 3 tab1_id,'table2_9' )


SELECT *
from table1 a
 left join table2 b on (a.id = b.tab1_id)

the answer
spoiler

id text1 text2 id2 tab1_id
Table1_1 1 1 1 table2_1
Table1_1 1 2 1 table2_2
Table1_1 1 3 1 table2_3
2 table1_2 4 2 table2_4
2 table1_2 5 2 table2_5
2 table1_2 6 2 table2_6
3 table1_3 7 3 table2_7
3 table1_3 8 3 table2_8
3 table1_3 9 3 table2_9
4 table1_4 (null) (null) (null)
5 table1_5 (null) (null) (null)


here's a
SELECT *
from table1 a
 left join table2 b on (a.id = b.tab1_id)
where b.tab1_id is not null


the answer
spoiler

id text1 text2 id2 tab1_id
Table1_1 1 1 1 table2_1
Table1_1 1 2 1 table2_2
Table1_1 1 3 1 table2_3
2 table1_2 4 2 table2_4
2 table1_2 5 2 table2_5
2 table1_2 6 2 table2_6
3 table1_3 7 3 table2_7
3 table1_3 8 3 table2_8
3 table1_3 9 3 table2_9


inner join
SELECT *
from table1 a
 inner join table2 b on (a.id = b.tab1_id)


the answer
spoiler
id text1 text2 id2 tab1_id
Table1_1 1 1 1 table2_1
Table1_1 1 2 1 table2_2
Table1_1 1 3 1 table2_3
2 table1_2 4 2 table2_4
2 table1_2 5 2 table2_5
2 table1_2 6 2 table2_6
3 table1_3 7 3 table2_7
3 table1_3 8 3 table2_8
3 table1_3 9 3 table2_9


Everything works, show that is not so ... - Althea24 commented on April 19th 20 at 12:22
@Althea24, yo, I even can not run. You have eight?
5.7 swear to the first row. - Marietta77 commented on April 19th 20 at 12:25
@Marietta77, I Have 8+ but it does not matter, just with this simple spreadsheet with the data so they do not create ...
If You are looking Left but you just have to limit the output for customers that do not want to see, you can enter a variable for the 5.7 or the window function for 8
It is also not difficult, only need to understand what You want - Althea24 commented on April 19th 20 at 12:28
@Althea24, variable-then enter... it cannot be as it turned out.
You can certainly question a school to decide one common pezaros plus two query depending on result. But damn, there must be a civilised way. - Marietta77 commented on April 19th 20 at 12:31
@Marietta77, Here's an example for my tables
select *
from (SELECT @row_num := CASE WHEN @row_num_val = a.id THEN @row_num+1
 WHEN (@row_num_val := a.id) IS NOT NULL THEN 1
 END pair_flag, a.*, b.*
 from (SELECT @row_num := null, @row_num_val := null) AS x
 table1 a
 left join table2 b on (a.id = b.tab1_id)) t1
where pair_flag <= 2


The answer
spoiler
pair_flag id text1 text2 id2 tab1_id
1.0 1 1 1 table1_1 table2_1
Table1_1 1 2.0 2 1 table2_2
1.0 2 4 2 table1_2 table2_4
2.0 5 2 2 table1_2 table2_5
1.0 3 7 3 table1_3 table2_7
2.0 3 8 3 table1_3 table2_8
1.0 4 table1_4 (null) (null) (null)
1.0 5 table1_5 (null) (null) (null)


As You can see in the response returns all but those who answer more than 2 displays only 2 response analog window functions

I just don't really understand Your problem ;-) - Althea24 commented on April 19th 20 at 12:34
I think it will be clear in more familiar tsql
...
from table1
left join table2 on table2.id=table1.id
where (@type='left' or table2.id is not null)
- wilfrid.Ri commented on April 19th 20 at 12:37
@wilfrid.Ri, here the variable is already superfluous, and table2.id is not null was higher and did not load. - Marietta77 commented on April 19th 20 at 12:40
@Marietta77, I have years of work. What am I doing wrong? ) - wilfrid.Ri commented on April 19th 20 at 12:43
@Marietta77If slovami "didn't work" do You understand the logic which You have in mind, it is possible.
What You describe in the post, it should cover.
If You have utochneniya or examples, try to present them. You may offer another solution, my crystal ball now shows the fog. - Althea24 commented on April 19th 20 at 12:46
@Althea24, thanks for the help, I'm happy for Your crystal ball. Obviously, You were born a DBA, and I'm happy for You as well. - Marietta77 commented on April 19th 20 at 12:49
@Althea24, all went according to your way, with adjustments for specifico.
Maribo ku. - Marietta77 commented on April 19th 20 at 12:52
April 19th 20 at 12:12
IF (subquery) = 0
 SELECT ... LEFT JOIN
ELSE
 SELECT ... INNER JOIN
no of course... and so, too, not the channel - Marietta77 commented on April 19th 20 at 12:15

Find more questions by tags MySQL