Matlab Database Toolbox

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

Answer 1

[Geoff]

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!

[Brian]

Thanks. I'm actually fairly familiar with SQL but I've run into a weird issue where the database that I'm querying will lock me out if I get the SQL wrong 3 times. The queries are fairly complex in their entirety and I am passing multiple variables in different formats as my parameters. I was just trying to make sure that my SQL was spot on before adding my variables to try and prevent getting continually locked out.

I was also a bit unclear on how Matlab was interpreting an "_" as part of the table name. It looked like it was actually looking for a period rather than the underscore. I'll get it figured out.

-Brian

[Geoff]

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%';

Answer 2

[Leah]

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.

[Brian]

I know I can just write the SQL, but writing SQL is not my strength. I was hoping to cheat and use the builder.

[Leah]

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.