Query not Returning Results

G

Guest

Hi

Im fairly new to all this so please bear with me - and forgive me if there
is a simple solution

i have a database with nine tables:

pk = primary key, fk = foreign key

deal (pk = d_id)(fk = V_LU=vehicle.v_id, C_LU=customer.c_id,
S_LU=supplier.s_id, SM_LU=salesman.s_id, F_LU=finance.f_id)
vehicle (pk = v_id)
customer (pk = c_id)
supplier (pk = s_id)
salesman (pk = sm_id)
finance (pk = f_id)
payments (pk = p_id)(fk = p_d_id=deal.d_id)
payments made (pk = pm_id)(fk = pm_d_id=deal.d_id)
additions (pk = a_id)(fk = a_id=deal.d_id)

basically i can add all details from a form and all information is stored
correctly (as far as im aware)
i wish to extract all data using the customer.c_id field to withdraw all
information.

When i come to running the query (below) i simply get no results returned?

SELECT deal.[D_Id], customer.Cust_Id, additions.Description,
additions.Supplier, additions.Date_, additions.Cost, payments.[Source:],
payments.[Date:], payments.[Method:], payments.[Amount:],
payments_made.Date_, payments_made.Method, payments_made.[Cheque No],
payments_made.Amount, vehicle.V_Type, vehicle.V_Reg, vehicle.V_Make,
vehicle.V_Model, vehicle.V_Derv, vehicle.V_Mileage, vehicle.V_Colour,
vehicle.V_History, vehicle.V_Fuel, vehicle.V_DOR, vehicle.V_Radio,
vehicle.V_Key, vehicle.V_Chassis, vehicle.V_Dr, vehicle.V_typeV
FROM vehicle INNER JOIN (supplier INNER JOIN (salesman INNER JOIN (((finance
INNER JOIN (customer INNER JOIN (deal INNER JOIN additions ON deal.D_Id =
additions.a_d_id) ON customer.Cust_Id = deal.C_LU) ON finance.F_Id =
deal.F_LU) INNER JOIN payments ON deal.D_Id = payments.p_d_id) INNER JOIN
payments_made ON deal.D_Id = payments_made.pm_d_id) ON salesman.SM_Id =
deal.SM_LU) ON supplier.S_Id = deal.S_LU) ON vehicle.V_Id = deal.V_LU
WHERE (((customer.Cust_Id)=3));

(i have also tried running the query without any criteria and still got no
results - i have checked that there is data stored in the tables)

Is there an error with my Select Statement? have i constructed my
relationships/primary or foreign keys incorrectly?

Any help or pointers would be greatly appreciated!

If you require any other details please let me know!

Regards

Daniel
 
G

Guest

If any of your join fields are NULL, then you will not get a record returned
using the INNER JOINs. You might want to try using outer joins instead of
inner joins to identify where the NULL values exist.

HTH
Dale
 
G

Guest

Hi Dale

Reading that through i made some adjustments to my select statement -
Changing all INNER Joins to OUTER joins but when run a Synax Error was
reported on all OUTER references!

The Error only seems to happen when there are null fields - Any other
suggestions?


Dale Fye said:
If any of your join fields are NULL, then you will not get a record returned
using the INNER JOINs. You might want to try using outer joins instead of
inner joins to identify where the NULL values exist.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Drodway said:
Hi

Im fairly new to all this so please bear with me - and forgive me if there
is a simple solution

i have a database with nine tables:

pk = primary key, fk = foreign key

deal (pk = d_id)(fk = V_LU=vehicle.v_id, C_LU=customer.c_id,
S_LU=supplier.s_id, SM_LU=salesman.s_id, F_LU=finance.f_id)
vehicle (pk = v_id)
customer (pk = c_id)
supplier (pk = s_id)
salesman (pk = sm_id)
finance (pk = f_id)
payments (pk = p_id)(fk = p_d_id=deal.d_id)
payments made (pk = pm_id)(fk = pm_d_id=deal.d_id)
additions (pk = a_id)(fk = a_id=deal.d_id)

basically i can add all details from a form and all information is stored
correctly (as far as im aware)
i wish to extract all data using the customer.c_id field to withdraw all
information.

When i come to running the query (below) i simply get no results returned?

SELECT deal.[D_Id], customer.Cust_Id, additions.Description,
additions.Supplier, additions.Date_, additions.Cost, payments.[Source:],
payments.[Date:], payments.[Method:], payments.[Amount:],
payments_made.Date_, payments_made.Method, payments_made.[Cheque No],
payments_made.Amount, vehicle.V_Type, vehicle.V_Reg, vehicle.V_Make,
vehicle.V_Model, vehicle.V_Derv, vehicle.V_Mileage, vehicle.V_Colour,
vehicle.V_History, vehicle.V_Fuel, vehicle.V_DOR, vehicle.V_Radio,
vehicle.V_Key, vehicle.V_Chassis, vehicle.V_Dr, vehicle.V_typeV
FROM vehicle INNER JOIN (supplier INNER JOIN (salesman INNER JOIN (((finance
INNER JOIN (customer INNER JOIN (deal INNER JOIN additions ON deal.D_Id =
additions.a_d_id) ON customer.Cust_Id = deal.C_LU) ON finance.F_Id =
deal.F_LU) INNER JOIN payments ON deal.D_Id = payments.p_d_id) INNER JOIN
payments_made ON deal.D_Id = payments_made.pm_d_id) ON salesman.SM_Id =
deal.SM_LU) ON supplier.S_Id = deal.S_LU) ON vehicle.V_Id = deal.V_LU
WHERE (((customer.Cust_Id)=3));

(i have also tried running the query without any criteria and still got no
results - i have checked that there is data stored in the tables)

Is there an error with my Select Statement? have i constructed my
relationships/primary or foreign keys incorrectly?

Any help or pointers would be greatly appreciated!

If you require any other details please let me know!

Regards

Daniel
Click to expand...
Click to expand...
 
D

Dale Fye

When I said Outer Join, I meant Left or Right JOIN. Can you post your
latest SQL so I can take another look at it.

Dale

Drodway said:
Hi Dale

Reading that through i made some adjustments to my select statement -
Changing all INNER Joins to OUTER joins but when run a Synax Error was
reported on all OUTER references!

The Error only seems to happen when there are null fields - Any other
suggestions?


Dale Fye said:
If any of your join fields are NULL, then you will not get a record
returned
using the INNER JOINs. You might want to try using outer joins instead
of
inner joins to identify where the NULL values exist.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Drodway said:
Hi

Im fairly new to all this so please bear with me - and forgive me if
there
is a simple solution

i have a database with nine tables:

pk = primary key, fk = foreign key

deal (pk = d_id)(fk = V_LU=vehicle.v_id, C_LU=customer.c_id,
S_LU=supplier.s_id, SM_LU=salesman.s_id, F_LU=finance.f_id)
vehicle (pk = v_id)
customer (pk = c_id)
supplier (pk = s_id)
salesman (pk = sm_id)
finance (pk = f_id)
payments (pk = p_id)(fk = p_d_id=deal.d_id)
payments made (pk = pm_id)(fk = pm_d_id=deal.d_id)
additions (pk = a_id)(fk = a_id=deal.d_id)

basically i can add all details from a form and all information is
stored
correctly (as far as im aware)
i wish to extract all data using the customer.c_id field to withdraw
all
information.

When i come to running the query (below) i simply get no results
returned?

SELECT deal.[D_Id], customer.Cust_Id, additions.Description,
additions.Supplier, additions.Date_, additions.Cost,
payments.[Source:],
payments.[Date:], payments.[Method:], payments.[Amount:],
payments_made.Date_, payments_made.Method, payments_made.[Cheque No],
payments_made.Amount, vehicle.V_Type, vehicle.V_Reg, vehicle.V_Make,
vehicle.V_Model, vehicle.V_Derv, vehicle.V_Mileage, vehicle.V_Colour,
vehicle.V_History, vehicle.V_Fuel, vehicle.V_DOR, vehicle.V_Radio,
vehicle.V_Key, vehicle.V_Chassis, vehicle.V_Dr, vehicle.V_typeV
FROM vehicle INNER JOIN (supplier INNER JOIN (salesman INNER JOIN
(((finance
INNER JOIN (customer INNER JOIN (deal INNER JOIN additions ON deal.D_Id
=
additions.a_d_id) ON customer.Cust_Id = deal.C_LU) ON finance.F_Id =
deal.F_LU) INNER JOIN payments ON deal.D_Id = payments.p_d_id) INNER
JOIN
payments_made ON deal.D_Id = payments_made.pm_d_id) ON salesman.SM_Id =
deal.SM_LU) ON supplier.S_Id = deal.S_LU) ON vehicle.V_Id = deal.V_LU
WHERE (((customer.Cust_Id)=3));

(i have also tried running the query without any criteria and still got
no
results - i have checked that there is data stored in the tables)

Is there an error with my Select Statement? have i constructed my
relationships/primary or foreign keys incorrectly?

Any help or pointers would be greatly appreciated!

If you require any other details please let me know!

Regards

Daniel
Click to expand...
Click to expand...
Click to expand...
 
Top