Solved

Adoquery sql  left join does not work

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 28

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 28

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

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 28

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 28

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 28

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

770 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