Solved

Adoquery sql  left join does not work

Posted on 2016-11-28
25
80 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 25

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
 
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 25

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 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 25

Expert Comment

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

Select
COUNT(*)  as cnt
From
  ABONNE
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 25

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 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 25

Expert Comment

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

Expert Comment

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

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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…
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 …
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…

861 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

25 Experts available now in Live!

Get 1:1 Help Now