| Summary: | Add support for JOIN in Query Designer | ||
|---|---|---|---|
| Product: | [Applications] KEXI | Reporter: | Peter Riches <kexi> |
| Component: | Queries | Assignee: | Kexi Bugs <kexi-bugs> |
| Status: | CONFIRMED --- | ||
| Severity: | wishlist | CC: | kde_richard, staniek |
| Priority: | NOR | ||
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Platform: | Fedora RPMs | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed In: | ||
| Sentry Crash Report: | |||
Dzien dobry! Dzekuje bardze na program "kexi" - jext bardze dobrze. Przeprasham - Mowie tylko troche po jezika Polska. <pl> Witam. Fajnie, że mówisz po polsku! </pl> I accept the report (it's been a TODO anyway). Kexi uses so-called parser to recognize your SQL commands in the SQL view. JOINs are not supported yet, but you can use the equivalent - WHERE clause. For your example, use: SELECT company.company_name, contact.contact_name FROM company, contact WHERE contact.company_id = company.company_id; In general Kexi has to "understand" any statement before it is sent to, say, MySQL server. That's why you can find many statements working at server side and not at Kexi side. Suggestion: when adding JOIN support, please ensure table aliases are supported, otherwise one can't do a self-join. > JOIN support
Example for the above query:
SELECT company.company_name, contact.contact_name FROM company c JOIN contact d ON c.company_id = d.company_id;
(note the 'company c JOIN contact d')
Note: the above alias in JOIN is essential for self-join queries, example "SELECT ... FROM company c1 JOIN company c1 ..." Err, "SELECT ... FROM company c1 JOIN company c2 ..." |
Version: Kexi 1.1.1 (KOffice 1.6.1) (using KDE KDE 3.5.5) Installed from: Fedora RPMs Compiler: pre-compiled by Red Hat (Fedora) OS: Linux There are two tables, company and contact. Company has a company_name field and a company_id field (which is the primary key). Contact has a contact_name field and a company_id field which defines which company the contact works at. This query should pair the names up: SELECT company.company_name, contact.contact_name FROM company JOIN contact ON contact.company_id = company.company_id; The query designer says that the query is incorrect. (Unknown table "contact".) The same query works fine from the MySql command line: mysql> SELECT company.company_name, contact.contact_name -> FROM -> company JOIN contact -> ON contact.company_id = company.company_id; +-----------------------------+-----------------+ | company_name | contact_name | +-----------------------------+-----------------+ | Clarkson Owens | Kate Winslett | | Riscy Electrical | | | Tartlet Quick Parcels Ltd. | Fred Taylor | | Allied Leccy Limited | David Dogscombe | | Donaught Wales | Rob Folks | | CMBE Electrical | | | Louis Electrical Limited | Tim Louis | | Donaught Wales | Debbey Mulls | | Chorus | n/a | +-----------------------------+-----------------+ 9 rows in set (0.00 sec) (names changed for confidentiality)