Solved

Adoquery sql  left join does not work

Posted on 2016-11-28
25
138 Views
Last Modified: 2016-12-01
Hi everybody
I have a database that contains 03 tables each table they also contain thousands of records, I create a text file that contains the lines of my query and that I named SQL.txt as attachments
My query contain the following lines:

Form1.Adoquery1.Close;
Form1.Adoquery1.sql.clear;
Form1.ADOQuery1.SQL.LoadFromFile ( 'sql.txt');
Form1.ADOQuery1.Parameters.ParamByName ( 'NUMAB'). Value: = Form1.DBLookupComboBox2.Text + '%';
Form1.ADOQuery1.Parameters.ParamByName ( 'COD'). Value: = Form1.DBLookupComboBox1.Text;
Form1.ADOQuery1.Open;


The problem when I execute my query sends nothing but when I remove the following line from my Sql.txt file everything works?

When I delete one of these two lines:
Left Join RUE On ABONNE.CODRUE = RUE.CODRUE
Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB
sql.txt
0
Comment
Question by:lounnaci ahmed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 7
  • +1
25 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41904128
why do you add form1 ?
if this inside a method of TForm1 then don't add Form1 in front of every line

did you test your query in your database query tool ?
in access itself ?

does the query work there ?

case sensitive fields ?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41904150
Check if below query is giving you the results... Also after removing the where clause.

Select
  ABONNE.NUMAB,
  ABONNE.RAISOC,
  RUE.NOUVNOM,
  ABONNE.BLOC,
  ABONNE.NDOM,
  ABONMENT.SEC
From
  ABONNE
  Left Join RUE On ABONNE.CODRUE = RUE.CODRUE 
  Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB
Where
  ABONNE.NUMAB Like :NUMAB And
  ABONNE.CODCAIS = :COD

Open in new window

0
 

Author Comment

by:lounnaci ahmed
ID: 41904152
Thank you Geert Gruwez my query works as soon as I remove a line either
Left Join RUE On ABONNE.CODRUE = RUE.CODRUE or this one
Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB
But it is impossible to operate both
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41904171
i don't see any added value of Pawan's comment
he posted your sql in a code piece, that's it

the left join clauses are optional and should not limit the rows returned, on contrary they could result in more rows


what result do you get from this ?

Select
  count(*)
From
  ABONNE
Where
  ABONNE.NUMAB Like :NUMAB And
  ABONNE.CODCAIS = :COD

Open in new window

1
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41904193
another thing ... check your sql.txt file for odd characters
with notepad++ or a similar editor, you can see all the characters
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 41904214
With the LIKE I believe you need a wildcard.  Otherwise I believe it is the same as an EQUAL compare

mlmcc
0
 

Author Comment

by:lounnaci ahmed
ID: 41904306
Hi Geert Gruwez
When I use your line of code

Select
  count(*)
From
  ABONNE
Where
  ABONNE.NUMAB Like :NUMAB And
  ABONNE.CODCAIS = :COD

Open in new window



I get a value equal to 2407
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41904359
what does this give :

Select
  ABONNE.NUMAB,
  ABONNE.CODRUE,
  COUNT(*)
From
  ABONNE
  Left Join RUE On ABONNE.CODRUE = RUE.CODRUE 
  Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB
group by ABONNE.NUMAB, ABONNE.CODRUE
having count(*) <> 1

Open in new window


this should give all the special ones
0
 

Author Comment

by:lounnaci ahmed
ID: 41905549
The code you suggested to me Mr. Geert Gruwez
Select
   ABONNE.NUMAB,
   ABONNE.CODRUE,
   COUNT (*)
From
   ABONNE
   Left Join RUE On ABONNE.CODRUE = RUE.CODRUE
   Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB
Group by ABONNE.NUMAB, ABONNE.CODRUE
Having count (*) <> 1

Open in new window

Do not return anything
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905569
Please run below and see if you get any rows..

1...

Select
*
From
  ABONNE
  Left Join RUE On ABONNE.CODRUE = RUE.CODRUE
  Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB


2..

Select
*
From
  ABONNE
  Left Join RUE On ABONNE.CODRUE = RUE.CODRUE
1
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41905596
that's very odd ... a left join doesn't limit records

sample in oracle
with 
  table_a as (select level as num, 1 as x from dual connect by level < 6),
  table_b as (select level +10 as num, 2 as y from dual connect by level < 6), 
  table_c as (select level +20 as num, 3 as z from dual connect by level < 6)
select * 
from table_a a 
  left join table_b b on a.num = b.num
  left join table_c c on a.num = c.num;       

table_A: 
NUM,X
1,1
2,1
3,1
4,1
5,1

table_b:
NUM,Y
11,2
12,2
13,2
14,2
15,2

table_c:
NUM,Z
21,3
22,3
23,3
24,3
25,3

the query result: 
NUM,X,NUM_1,Y,NUM_2,Z
4,1,,,,
5,1,,,,
3,1,,,,
1,1,,,,
2,1,,,,

Open in new window


you see ... all rows from table_a are returned, none of the rows of the other tables match, so all records of table A are retrieved

my guess is : your database doesn't work
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905600
Check .. how many rows are you getting from this ?

Select
COUNT(*)  as cnt
From
  ABONNE
0
 

Author Comment

by:lounnaci ahmed
ID: 41905811
Hi Pawan Kumar Khowal
The following code does not return any result

Select
*
From
   ABONNE
   Left Join RUE On ABONNE.CODRUE = RUE.CODRUE
   Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB

Open in new window


But the following code

Select
*
From
   ABONNE
   Left Join RUE On ABONNE.CODRUE = RUE.CODRUE

Open in new window


  gives 100672 lines
0
 

Author Comment

by:lounnaci ahmed
ID: 41905819
And the following code
Select
COUNT (*) as cnt
From
ABONNE

Open in new window

When I run it gives 100672 records
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 41906027
What do you get from

Select
*
From
   ABONNE
  Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB

Open in new window


mlmcc
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41906063
Try this ...

It looks like you don't have any matching records.

Could you please post data from these tables so that we can check .
0
 

Author Comment

by:lounnaci ahmed
ID: 41907063
Hi mlmcc the code you suggested
Select
*
From
    ABONNE
   Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB

Open in new window


When I execute it returns 103119 records
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 41907290
I don't think it should matter but have you tried using ( )

Select
*
From
   (ABONNE
   Left Join RUE On ABONNE.CODRUE = RUE.CODRUE)
   Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB

Open in new window


mlmcc
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41908235
Could you please attach your DB so that we can check .
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41908254
lol, attach your db ...
i would ask which type it is

it is not normal that the 2 left joins do not work
the conditions in the joins don't even have to be met

even this should work:

Select
*
From
   ABONNE
   Left Join RUE On 1 = 0
   Left Join ABONMENT On 1 = 0

Open in new window


my sample works on oracle:
07:59:08 >with
07:59:09   2    table_a as (select level as num, 1 as x from dual connect by level < 6),
07:59:09   3    table_b as (select level +10 as num, 2 as y from dual connect by level < 6),
07:59:09   4    table_c as (select level +20 as num, 3 as z from dual connect by level < 6)
07:59:09   5  select *
07:59:09   6  from table_a a
07:59:09   7    left join table_b b on 1 = 0
07:59:09   8    left join table_c c on 1 = 0;

       NUM          X        NUM          Y N Z
---------- ---------- ---------- ---------- - -
         1          1
         2          1
         3          1
         4          1
         5          1

07:59:11 >

Open in new window


if the first code piece does not work, then your database is not compatible with the bare minimum requirements for a database
just a sample : https://en.wikipedia.org/wiki/SQL
0
 

Author Comment

by:lounnaci ahmed
ID: 41908871
hi mlmcc
Yes i used the () and it works impeccably

Select
*
From
   (ABONNE
   Left Join RUE On ABONNE.CODRUE = RUE.CODRUE)
   Left Join ABONMENT On ABONNE.NUMAB = ABONMENT.NUMAB

Open in new window


Thank you all
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41909063
Hi mlmcc,
Could you please put some light , why it worked with ().

Thanks in advance.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 41909068
I honestly don't know.  I really didn't expect it to make a difference.  The ( ) just specify the order of the joins.

mlmcc
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41909073
LOL. Thank you !!
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41909284
the reason is : access is not sql-ansi compatible
it's a stupid excuse for a database

http://nm1m.blogspot.be/2007/10/multiple-left-joins-in-ms-access.html
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Make the most of your online learning experience.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Progress
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question