Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
4
Erster Beitrag:
vor 5 Jahren, 2 Monaten
Letzter Beitrag:
vor 5 Jahren, 2 Monaten
Beteiligte Autoren:
Alexandre Leclerc, Arie

hfcs15 - outer join and where part

Startbeitrag von Arie am 21.05.2013 12:22

Hi,
I have a outer join query like this
SELECT * From Tbl_Rooms
LEFT OUTER JOIN Tbl_RoomTypes on Tbl_Rooms.RoomTypeID = Tbl_RoomTypes.RoomTypeID
WHERE Tbl_RoomTypes.LargeRoom = 1

The result is all rooms with LargeRoom = True
The result ALSO includes all room with NO roomtype at all.
These record onlye have data for the 'room', but no values (null's?!?) for the roomtype-fields. Because there is no roomtype linked.

As you can see the where-statement is on a field, which may be missing for some rooms. I would expect that these record are discarded, but they are not?!?!

Is this the efault behaviour of WD?

Antworten:

Hi Arie,

This is probably because all records of the LEFT table will be displayed even if there are not matching records on the right. And since there are no matching record, the WHERE clause cannot be verified. In that case it looks like that instead of removing the record as not satisfying the WHERE clause, it is simply added as respecting the LEFT JOIN.

Maybe this behaviour could be confirmed by asking the Free Technical Support. (Even if it is not the desired behaviour they will most probably not fix it because of compatibility with behaviour of previous versions.)

A fix could be to add in the WHERE clause to ignore all room that has no room type.

In fact, what you need is an INNER JOIN (i.e. the link made in the WHERE clause. See http://doc.pcsoft.fr/en-US/?2034007 on inner joins).

Best regards,
Alexandre Leclerc

Edit 1: Use inner join.

von Alexandre Leclerc - am 21.05.2013 13:51
Alexandre,
you are right about the inner join. My case is a bit more complex and I was trying to use the same code+query to handle all situations. Which is a bad idea in the first place, I know.
Still the query-engine behaves different from what I expect. I even wonder if it follows the standard...

von Arie - am 21.05.2013 15:05
Hi Arie,

And I agree with you. I've seen that on other SQL DB your query would have returned what you expected. This should be reported but the behaviour will probably not changed (or could / should be documented).

Best regards,
Alexandre Leclerc

von Alexandre Leclerc - am 21.05.2013 15:20
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.