Dynamic Data Assist Table Join

Hi,

I want to display in a dynamic table a recordset which joins 3 tables.

In lamens terms what id diff between Left, Right and Join ?

I want to show from say clients all my clients, also users and products.

not all users will have clients and not all clients will have a product yet so is Join the best way to get them together rather than LEFT ?

many thanks

See the following web page for a complete explanation and definition of the different join types:
Left_join#Left_outer_join


when you create a join the comparison has a table on the left and a table on the right:
INNER JOIN LeftTBL.IdColumn ON RightTBL.IDColumn

Whether to use a Right or left join depends on the table on the right or left, the data in each of those tables and the expected results.

Originally Said By: Jason Byrnes

  See the following web page for a complete explanation and definition of the different join types:
Left_join#Left_outer_join


when you create a join the comparison has a table on the left and a table on the right:
INNER JOIN LeftTBL.IdColumn ON RightTBL.IDColumn

Whether to use a Right or left join depends on the table on the right or left, the data in each of those tables and the expected results.  

thanks jason, i will have a loot at this. i have tried to understand by reading around before.

i take it the left table is the one you declare first in the query?

also how would you create multiple joins? so say i wanted to join my insurance table to clients and clients with users table so i could filter by userid?

thanks jason

the left table is the table to the left of the JOIN key word. the right table is the table to the right of the JOIN keyword

In this example:

SELECT *
FROM department LEFT OUTER JOIN employee
ON employee.DepartmentID = department.DepartmentID


the left table is department

the right table is employee.


look at it as:
SELECT *
FROM leftTable LEFT OUTER JOIN rightTable

I think perhaps a tool like Navicat would help you tremendously:
mysql_overview.html


navicat can be used to automatically generate complex join queries for you using a GUI.

WEbassist should bring to life again the old ADDT Qub to make these type of joins in an easy, going to the wish list :eek:

thanks jason, i have looked at this in the past and will again now.

as for sades re the Qub, i found it quite difficult to work the QUB, maybe i was not understanding properly but i could not do a simple join with it could you?

the rest of the addt was great but i am getting my head around WA extensions now thanks to the support.

cheers

Sorry, never used the QUB so I cannot comment.

Navicat i have had great success with though.

Originally Said By: mr hankey

  thanks jason, i have looked at this in the past and will again now.

as for sades re the Qub, i found it quite difficult to work the QUB, maybe i was not understanding properly but i could not do a simple join with it could you?

the rest of the addt was great but i am getting my head around WA extensions now thanks to the support.

cheers  

Yes it very simple you select the databases you wish to join then select on each the fields to display on the query and you drag and drop the fields you want to join, on the bottum it displays the code, and a preview of the join from there u can right click over the links of each join to configure to left join, right join etc... you can select distinct also, add sum coun min max also, do the where from there with a menu very similiar to the dreamweaver add query for filtering, then you just click save go to dreamweaver hit the refresh button on the Qub and it places all the code there and from there you can go to the advance of recordset to edit any stuff like add some RAND or orders etc... in other words you can join hundreds of tables (going crazy lol) in just a few seconds, the best part is the visual part of how to do it with its preview, check the pictures i uploaded playing with a test database.

Im to also managing my self around with webassist now that adobed killed addt, but webassist lacks on some parts to make life easy, im sad that the new anounced frame works seems to only come with the nice server include functions, just hope webassist some day makes more extensions to avoid the time consuming wich is why most of us use these extensions.