Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
10
Erster Beitrag:
vor 2 Jahren, 10 Monaten
Letzter Beitrag:
vor 2 Jahren, 10 Monaten
Beteiligte Autoren:
CCC2, Paul Turner, issah, Swissstephan, Ned!

WD and mySQL

Startbeitrag von Swissstephan am 16.08.2015 21: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

Antworten:

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!

von Ned! - am 17.08.2015 14:09
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

von issah - am 19.08.2015 23:40
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

von Swissstephan - am 20.08.2015 21:01
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 .

von CCC2 - am 20.08.2015 23:19
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

von Paul Turner - am 20.08.2015 23:38
for view , read this old blog. also mention in book about mysql perfromance
https://www.percona.com/blog/2007/08/12/mysql-view-as-performance-troublemaker/


for mssql, read this
http://troels.arvin.dk/db/rdbms/

in ms-sql support uniqueidentifier column while mysql don't have.

each database has their own good and bad , unless you are playing with 30GB+ database, most of the time you won't see the different .

von CCC2 - am 21.08.2015 03:48
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 .

von CCC2 - am 21.08.2015 04:27
Hi SwissStephan,
from your first post, you indicated needing a MySQL statement, so I assumed yo are using MySQL server.

regards

issah

von issah - am 21.08.2015 21:53
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

von Paul Turner - am 21.08.2015 23:31
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.