Hi,

Here is my problem:

table A

---x---- y--
1-08 | 10 |
2-08 | 20 |
3-08 | 30 |
4-08 | 40 |
5-08 | 50 |
6-08 | 60 |

table B

---x---- z-- q----
1-08 | 11 | 6 |
2-08 | 22 | 6 |
3-08 | 33 | 6 |
1-08 | 44 | 8 |
2-08 | 55 | 8 |
3-08 | 66 | 8 |

i need to create a left join in order to display a similar result, showing all values of x column of table A, and passing "q" value (ex.6) as session variable:

--x------ y--- z--- q
1-08 | 10 | 11 | 6 |
2-08 | 20 | 22 | 6 |
3-08 | 30 | 33 | 6 |
4-08 | 40 | 0 l 0 | ("0" or NULL)
5-08 | 50 | 0 | 0 |
6-08 | 60 | 0 | 0 |

If I create a query with query builder using A and B tables with left join relation, establishig "q" value as a variable, I get this result 🙁

x y z q
1-08 | 10 | 11 | 6 |
2-08 | 20 | 22 | 6 |
3-08 | 30 | 33 | 6 |
not showing other "x" column values of table A.

What should I do?

Tnx

    Hard to tell without seeing the SQL, but my guess is that in the second query you have q=6 in the WHERE clause. So that's all you get, rows where q=6.

      Here is the code of my example:

      SELECT
      a.x, a.y, b.z, b.q
      FROM
      a LEFT JOIN
      b ON a.x = b.x
      WHERE
      b.q = 6;

      😕

      Is it possible to create a view of table B filtered by a session variable and then use this view as a table to left join with table A ???? The use of variables in database tables and views is possible? (sorry but I am a newbie):xbones:

        Don't overthink it. The ON clause and the WHERE clause are just conditions for the JOIN and the result set respectively. Make the condition whatever you want.

        SELECT
`a`.`x`, `a`.`y`, `b`.`z`, `b`.`q`
FROM
`a` LEFT JOIN
`b` ON `a`.`x` = `b`.`x` AND `b`.`q` = 6;

          now it works !!!! :eek:


          Thank you Tomhath !!!!!

            Write a Reply...