• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Adoquery sql left join does not work

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
lounnaci ahmed
Asked:
lounnaci ahmed
  • 7
  • 7
  • 7
  • +1
1 Solution
 
Geert GOracle dbaCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
lounnaci ahmedAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Geert GOracle dbaCommented:
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
 
Geert GOracle dbaCommented:
another thing ... check your sql.txt file for odd characters
with notepad++ or a similar editor, you can see all the characters
0
 
mlmccCommented:
With the LIKE I believe you need a wildcard.  Otherwise I believe it is the same as an EQUAL compare

mlmcc
0
 
lounnaci ahmedAuthor Commented:
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
 
Geert GOracle dbaCommented:
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
 
lounnaci ahmedAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Geert GOracle dbaCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Check .. how many rows are you getting from this ?

Select
COUNT(*)  as cnt
From
  ABONNE
0
 
lounnaci ahmedAuthor Commented:
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
 
lounnaci ahmedAuthor Commented:
And the following code
Select
COUNT (*) as cnt
From
ABONNE

Open in new window

When I run it gives 100672 records
0
 
mlmccCommented:
What do you get from

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

Open in new window


mlmcc
0
 
Pawan KumarDatabase ExpertCommented:
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
 
lounnaci ahmedAuthor Commented:
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
 
mlmccCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Could you please attach your DB so that we can check .
0
 
Geert GOracle dbaCommented:
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
 
lounnaci ahmedAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Hi mlmcc,
Could you please put some light , why it worked with ().

Thanks in advance.
0
 
mlmccCommented:
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
 
Pawan KumarDatabase ExpertCommented:
LOL. Thank you !!
0
 
Geert GOracle dbaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 7
  • 7
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now