[ Requirements to understand the problem:
create table MyArt (ArtNo int not null primary key, Description varchar(30) not null)
create table MyStore (PalletID int not null primary key, ArtNo int not null)
insert into MyArt values ('1', 'Huge Box 100x200')
insert into MyArt values ('2', 'Huge Box 150x200')
insert into MyArt values ('3', 'Little Box 10x10')
insert into MyStore values ('100','1')
insert into MyStore values ('101','3')
]
The problem:
We join two tables and want to get all pallets that have a specific description.
We used a sql statement like this:
SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description FROM MyStore LEFT JOIN MyArt ON MyStore.ArtNo = MyArt.ArtNo WHERE MyArt.Description LIKE 'Huge%'
Result: 100 | 1 | Huge Box 100x200 101 | 3 |
! ! It seems like the WHERE statement only affects the MyArt table ! and NOT the whole join. Failure or feature?? !
Statement without WHERE:
SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description FROM MyStore LEFT JOIN MyArt ON MyStore.ArtNo = MyArt.ArtNo
Result (correct): 100 | 1 | Huge Box 100x200 101 | 3 | Little Box 10x10
If we try the following (LEFT JOIN/WHERE/=) it works:
SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description FROM MyStore LEFT JOIN MyArt ON MyStore.ArtNo = MyArt.ArtNo WHERE MyArt.Description = 'Huge Box 100x200'
Also "INNER JOIN/WHERE/LIKE" works:
SELECT MyStore.PalletID, MyStore.ArtNo, MyArt.Description FROM MyStore INNER JOIN MyArt ON MyStore.ArtNo = MyArt.ArtNo WHERE MyArt.Description LIKE 'Huge%'
Result in both cases correct: 100 | 1 | Huge Box 100x200
What's your opinion about this behaviour?
Yours sincerely,
Benjamin Lukner trinomix GmbH
_______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
