MATLAB Answers

Matlab Database Toolbox

6 views (last 30 days)
Brian
Brian on 2 May 2012
What is an easy way to create table joints using the querybuilder that is available for the database toolbox? I'm just looking for simple inner joins.
Thanks, Brian

Accepted Answer

Geoff
Geoff on 2 May 2012
Please don't be frightened of coding an SQL join. Look:
SELECT <fields>
FROM <table1>
JOIN <table2> ON <table1.key> = <table2.key>
But anyway, I just had a crack with querybuilder. Never used it before, and I'm not about to start.... It seems utterly underpowered for all but the world's most mundane query.
Select your tables to join (hold control-key and click to select multiple tables) then add a WHERE clause. Stupid window pops up. Select the field that is the primary key in one table, then in the edit box to the right of the '=' relation, you have to TYPE IN the name of the primary key for your other table. Click 'Apply', and you'll see the line appear in your 'Current clauses' box.
You also need to choose some fields back in the main query window, or nothing will show up in the 'SQL statement' box.
This is an inner join without using the JOIN keyword.
If you have aggregate keys on your tables you will have to repeat the exercise for each key, and join the clauses together with AND.
I strongly recommend you don't try anything more complex than this with querybuilder or you'll end up with an unjustified hatred of SQL. =)
Now, tell me whether it's easier to learn some basic SQL or battle with querybuilder!
  2 Comments
Geoff
Geoff on 3 May 2012
Underscore is a valid character for identifiers (tables, fields, aliases, variables) in SQL. The period is used as a delimiter to specify that a field belongs to a table (or alias). If you select two tables in a query and both have a field called 'id', then it is ambiguous and you need to fully qualify it by specifying the table.
eg:
SELECT t1.id, t2.name FROM table1 AS t1, table2 WHERE t1.id=table2.id;
Note I've shown how to alias a table name here (usually for brevity or clarity). You can do the same with fields. The 'AS' keyword is optional (you can just say 'table1 t1' instead of 'table1 AS t1').
Name qualification is not required if there's no ambiguity:
SELECT id, name FROM table2 WHERE name LIKE 'Fred%';

Sign in to comment.

More Answers (1)

Leah
Leah on 2 May 2012
I don't see a join in the tool you can just write the SQL for it. Generate the M-file from a basic query then just add you join in.
  2 Comments
Leah
Leah on 2 May 2012
check out the section titled "Creating Queries That Include Results from Multiple Tables"
http://www.mathworks.com/help/toolbox/database/ug/f7-35861.html
I think learning the SQL would be easier.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by