Hi Ryan, > I would have thought this would be easy by aliasing the CONTACT table > multiple times as different names, and then referring to the different > names in my SELECT statement, but this isn't working. Can anyone see a > solution to this?
Certainly heading in the right direction. My guess is all you need to do is double click on the links in your query, and select "Include all records from project and only those records ..." - i.e. an outer join. HTH Phil. ----- Original Message ----- From: "Ryan Sabir" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, May 05, 2004 11:46 AM Subject: [cfaussie] SQL Query Help > Hey all, > > Need some help with a complicated join... > > I have tables: > > PROJECT > project_id > project_name > project_date > > CONTACT > contact_id > contact_name > contact_type > > PROJECTCONTACTS > project_id > contact_id > > CONTACTTYPE > contactType_id > contactType_name > > The contact types are, for example: > 1: Supplier > 2: Manufacturer > 3: Distributor > > now I want to run a query that returns ONE row for each project like > this: > project_id project_name supp_name manu_name dist_name > > Also, as you can see it's an N->M relationship.. a project might not > necessarily have any contacts connected to it, or it may have one of > each. > > I would have thought this would be easy by aliasing the CONTACT table > multiple times as different names, and then referring to the different > names in my SELECT statement, but this isn't working. Can anyone see a > solution to this? > > The other problem with this is that it needs to work in Access.. I'm > not sure if there is some limitation there that I won't be able to get > around... > > thanks. > > > > > ----------------------- > Ryan Sabir > Newgency Pty Ltd > 2a Broughton St > Paddington 2021 > Sydney, Australia > Ph (02) 9331 2133 > Fax (02) 9331 5199 > Mobile: 0411 512 454 > http://www.newgency.com/index.cfm?referer=rysig > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
