필터 지우기
필터 지우기

Matlab Database Toolbox

조회 수: 4 (최근 30일)
Brian
Brian 2012년 5월 2일
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

채택된 답변

Geoff
Geoff 2012년 5월 2일
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
Brian
Brian 2012년 5월 3일
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
Geoff 2012년 5월 3일
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%';

댓글을 달려면 로그인하십시오.

추가 답변 (1개)

Leah
Leah 2012년 5월 2일
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
Brian
Brian 2012년 5월 2일
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
Leah 2012년 5월 2일
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.

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Database Toolbox에 대해 자세히 알아보기

Community Treasure Hunt

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

Start Hunting!

Translated by