I'd replace your where clauses with JOIN stetements to ensure you get the
right type of join.
The types of joins you can use are:
INNER JOIN (pretty much the same as a where)
LEFT OUTER JOIN - Include all rows from the left hand table but only those
that match from the right hand one.
RIGHT OUTER JOIN - Include all rows from the right hand table but only those
that match from the left hand one.
Which join to use depends on the relationships between the tables. i.e.
what are the primary keys in each table and what are the foreign keys?
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
------------------------------------------------------------------
-----Original Message-----
From: Ray, James A [mailto:[EMAIL PROTECTED]]
Sent: 11 October 2000 15:51
To: CF-Talk
Subject: Query Question
I have a complex query and need a little help in directions. I have 5 tables
that I need to grab various information from . Some of the tables will only
have have 1 record that will match, others can have several that do. The
problem comes from those tables that have more then one match. The results
will produce eveything I need, but records get duplicated. I hope this is
enough information. I used a join query by the way
SELECT
A.Mission,A.PN,A.SN,A.Item,B.PN,B.Item,C.PN,C.Life,D.PN,D.SN.C.Value,D.PN,D.
SN,D.Date
FROM Table1 A,Table2 B ,Table3 C , Table4 D,Table5 E
WHERE (A.MISSION_NAME = '#Trim(Form.MISSION_NAME)#')
AND (A.PN= B.PN) AND (A.PN= C.PN) AND (A.PN = D.PN) AND (A.SN = D.SN) AND
(A.PN = E.PN) AND (A.SN =E.SN)
Here is a basic laytout of the table.
Table 1
Mission,PN,SN,Item
Table 2
PN,Item
Table 3
PN,Life
Table 4
PN,SN,Value
PN,SN,Value
PN,SN,Value
Table 5
PN,SN,Date
PN,SN,Date
PN,SN,Date
The results should be something like this.
Mission
PN,SN,Item,Life,Value,Date
PN,SN,Item,Life,Value,Date
PN,SN,Item,Life,Value,Date
Thank you
Jim Ray
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
