|
| Iniciado por guest, 16,ago. 2015 23:57 - 9 respuestas |
| |
| | | |
|
| |
| Publicado el 16,agosto 2015 - 23:57 |
Hello
guess it's an easy Problem but since my mysql knowledge is Zero I have no clue.
I Need a mysql Statement which Looks at the Moment like this and it doesn't work (created by WD):
SELECT
Ausstellung.VernissageDatum AS VernissageDatum,
Ausstellung.Titel AS Titel,
Ausstellung.FlyeriD AS FlyeriD,
Ausstellung.AusstellungID AS AussstellungID,
Kuenstler.Name AS Name,
Kuenstler.Vorname AS Vorname,
AusstellungKuenstler.AusstellungKuenstlerID AS AusstellungKuenstlerID,
AusstellungKuenstler.Objekte AS Objekte
FROM
AusstellungKuenstler RIGHT OUTER JOIN Ausstellung ON Ausstellung.AusstellungID = AusstellungKuenstler.AusstellungID,
AusstellungKuenstler INNER JOIN Kuenstler ON Kuenstler.KuenstlerID = AusstellungKuenstler.KuenstlerID
ORDER BY
VernissageDatum DESC
I have three tables:
Ausstellung=Exhibition
Kuenstler=Artist
AusstellungKuenstler=ExhibitionArtist (supporttable since an Exhibition can have several artists and an Artist can have several exhibitions.
All I want is a record for all AUSSTELLUNG whether there is an AUSSTELLUNGKUENSTLER or not and if there is one I Need Name and title from KUENSTLER
Any help is appreciated. Thx in advance
Stephan |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,agosto 2015 - 16:09 |
Hi Stephan,
You could try something like...
SELECT Ausstellung.VernissageDatum AS VernissageDatum, Ausstellung.Titel AS Titel, Ausstellung.FlyeriD AS FlyeriD, Ausstellung.AusstellungID AS AussstellungID, Kuenstler.Name AS Name, Kuenstler.Vorname AS Vorname, AusstellungKuenstler.AusstellungKuenstlerID AS AusstellungKuenstlerID, AusstellungKuenstler.Objekte AS Objekte FROM Ausstellung LEFT JOIN AusstellungKuenstler ON Ausstellung.AusstellungID = AusstellungKuenstler.AusstellungID LEFT JOIN Kuenstler ON Kuenstler.KuenstlerID = AusstellungKuenstler.KuenstlerID ORDER BY VernissageDatum DESC
Thanks
Ned! |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,agosto 2015 - 01:40 |
Hi Swissstephan,
whenever I have more than two tables being used in a query...especially with outer joins, I find it better to create a view in MySQL and import the view definition. WD will see it as a table and you can specify parameters....much easier to manage and more reliable. Also, this allows you to perform updates on the view and overcome WD's limitation on updating multiple tables.
Basically allows you to leverage the superior SQL implementation in MySQL or any other database server for that matter.
regards
issah |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,agosto 2015 - 23:01 |
Hi Ned, hi Issah
Thanks for your answers. I tried Ned's Version and it seems, it gives the desired result.
Issah, if possible I stay with my H/F SQL 
Kind regards and thx again
Stephan |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,agosto 2015 - 01:19 |
Hi Stephen,
H?F SQL is a junk . it is ok for small and simple db. if your db consist over 50+ tables with over 30GB then you better use others.
my sql is good for speed , the trade back is alot functions/datatype not supported specially in T-sql . if you need complicated T-sql , i suggest ms-sql .
in wd , turn off the auto correction will allow you to run standard sql .
in mysql , view is actually a table. use wisely or will slow down your db . |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,agosto 2015 - 01:38 |
I use MySQL a lot - find it much faster and more robust that HFSQL.
I'm interested in your comment that views are actually tables in MySQL. What justification do you have for that comment - I have seen no evidence of that. Certainly when I create views in mysql they are available instantly, no time required for construction of a table and associated indexes. Also no storage space is lost, so that also suggests they are not stored as a table.
In terms of datatypes, MySQL seems comparable to MSSQL (I use that as well). Functions can be created within MySQL, so I also fail to understand your comment that they are limited.
Rgds
Paul |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,agosto 2015 - 05:48 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,agosto 2015 - 06:27 |
this is my recommendation of database server .
a) if working with big project (over 50 tables and storage above 2gb ) and complicated function and advance t-sql and has huge funding
1.oracle
2. MS-SQL
b) no huge funding
1.postgresql
2.mysql
why postgresql better than mysql ? , in term of speed mysql is better but postgresql has more functions that mysql not support .
for few example
- in security mysql is very simple compare to postgresql (very advance) .
- schema in postgresql which allow grouping of table within on database
- postgresql allow default value in function/sp , while mysql cannot .
c) for flat file
1. sqlite
2. clarion files
3. HF classic
why clarion files better than HF classic . that is because it's extreme rare for clarion file become corrupt. that not the case with HF classic .
H/F SQL is still a junk. use this only because you system using HF classic and you wish to use on multi users enviroment . if possible don't waste your time in this db . |
| |
| |
| | | |
|
| | |
| |
| Publicado el 21,agosto 2015 - 23:53 |
Hi SwissStephan,
from your first post, you indicated needing a MySQL statement, so I assumed yo are using MySQL server.
regards
issah |
| |
| |
| | | |
|
| | |
| |
| Publicado el 22,agosto 2015 - 01:31 |
HI CCC2
Thanks for posting that article. I'll keep an eye on the views I'm using with MySQL - which is a lot. However I always test the view performance against the native SELECT, and have yet to find an issue. Maybe I'm just lucky so far!
Rgds
Paul |
| |
| |
| | | |
|
| | | | |
| | |
|