Re: [SQL] Select query - Someone put me out of my misery please

From: Chris Hope (blackhole_at_electrictoolbox.com)
Date: 10/25/04 

Date: Tue, 26 Oct 2004 08:23:44 +1300

Joshua wrote:

> If someone can please put me out of my misery! I have been working on
> this SQL select query for a good few hours now.
>
> Four tables (fields simplified for post):
>
> Roles - roles_id, title, locations_id
> Locations - locations_id, name
> Contacts - contacts_id, name, tel
> Roles_Contacts - roles_id, contacts_id
>
> Brief description:
>
> A role has one location. A role can have one or more contacts.
>
> THE select query:
>
> For a given role id, need to select all role fields, name of location,
> and all fields for each contact belonging to the role.
>
> Where I've got so far:
>
> I have 5 roles in the roles table. 1 contact in the contacts table.
> Each role is (obviously) assigned just the 1 contact. 3 locations in
> the locations table. If I want to select the information as described
> above for the role with id 4 -
>
> select
> contacts.css_contacts_middlename ,
> locations.css_locations_name ,
> roles_contacts.css_help_desk_roles_id ,
> roles.css_help_desk_roles_title ,
> contacts.css_contacts_surname ,
> roles.css_help_desk_roles_id ,
> roles.css_locations_id ,
> contacts.css_contacts_forename
>
> from
> css_help_desk_roles roles ,
> css_locations locations ,
> css_help_desk_roles_contacts roles_contacts ,
> css_contacts contacts
> where
> locations.css_locations_id=roles.css_locations_id
> and
> contacts.css_contacts_id=roles_contacts.css_contacts_id
> and
> roles.css_help_desk_roles_id = '4'
>
> Current Result:
>
> I think I'm almost there, see below, but I get repeated data -
> repeated for the number of roles there are in the roles table (5).
>
> One thing to note is the 3rd field along (css_help_desk_roles_id)
> seems to be the culprit as it is the one that is listing all the
> unique role_id 's from the role table rows.

I can see an immediate problem without even analysing your queries too much.

You have joined css_locations and css_help_desk_roles, and css_contacts with
css_help_desk_roles_contacts, but there is no other relationship between
these joined tables, which is why you are getting repeated results.

Sort out the joins so all the tables have joined columns and you should
solve your problem. 

-- 
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)