Solved

Adoquery sql  left join does not work

Posted on 2016-11-28
25
50 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
  • 7
  • 7
  • 7
  • +1
25 Comments
 
LVL 37

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 18

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 37

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 37

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 100

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 37

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 18

Expert Comment

by:Pawan Kumar Khowal
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 37

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 18

Expert Comment

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

Select
COUNT(*)  as cnt
From
  ABONNE
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 100

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 18

Expert Comment

by:Pawan Kumar Khowal
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 100

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 18

Expert Comment

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

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 18

Expert Comment

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

Thanks in advance.
0
 
LVL 100

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 18

Expert Comment

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

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now