Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: Litle problem with a query Any help

    Hello to every one

    I have three tables and iam just trying to create a view
    i have created the view but what i want to do
    is to retrieve summarised
    customer data including Bussiness name, one of their contacts

    but the customer 1 has more than one contact so when iam
    eXecuting the query have 1 customer with three contacts and 1 with
    1 as expected but i only want 1 contact to display for a customer 1
    one for customer 2 etc

    do i have to use any filter?

    I have tried this

    SELECT dbo.Customers.BussinessName, dbo.Contacts.Name, COUNT(*) AS Expr1, dbo.Cust2Cont.Cust2ContID, dbo.Cust2Cont.Cont2CustID
    FROM dbo.Contacts INNER JOIN
    dbo.Cust2Cont ON dbo.Contacts.ContactsID = dbo.Cust2Cont.Cont2CustID INNER JOIN
    dbo.Customers ON dbo.Cust2Cont.Cust2ContID = dbo.Customers.CustomerID
    GROUP BY dbo.Customers.BussinessName, dbo.Contacts.Name, dbo.Cust2Cont.Cust2ContID, dbo.Cust2Cont.Cont2CustID

    Any Idea?
    Attached Thumbnails Attached Thumbnails Image1.bmp   Image2.bmp   Image3.bmp   Image4.bmp  

  2. #2
    Join Date
    Apr 2010
    Posts
    2

    temp table?

    how about creating a temporary table to filter the cuntacts down to one per customer:

    declare @temp table (CustomerID int, ContactsID int)

    insert @temp (CustomerID int, ContactsID )
    select dbo.Customers.CustomerID, min(dbo.Contacts.contactsID)
    FROM dbo.Contacts INNER JOIN
    dbo.Cust2Cont ON dbo.Contacts.ContactsID = dbo.Cust2Cont.Cont2CustID INNER JOIN
    dbo.Customers ON dbo.Cust2Cont.Cust2ContID = dbo.Customers.CustomerID
    group by dbo.Customers.CustomerID

    then

    SELECT dbo.Customers.BussinessName, dbo.Contacts.Name, COUNT(*) AS Expr1, dbo.Customers.CustomerID,dbo.Contacts.ContactsID
    FROM dbo.Contacts INNER JOIN @temp on
    dbo.Contacts.ContactsID = @temp.contactsID
    INNER JOIN dbo.Customers ON @temp.customerID= dbo.Customers.CustomerID

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    Thanks

    Thanks Man deep apreciated

    Works fine even if you not create a temp table

    I was try to create one but an error was coming through
    so all i am getting out right now is the bussinesName and
    one contact ID with no names or details of the contacts table
    but i think the contactID will do the job, How about if i want to display
    a name of that contact ?

    How about if i want to create a stored procedure that will also create a default contact detail
    Any ideas

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Can't test this 'cause I don't have your data, so there may be minor issues with this that you will have to clean-up. It makes an assumption about the contact that you are selecting for a customer is the contact with the earliest ContactsID value. You can make the MIN a MAX and get the contact with the latest ContactsID value. Notice also that I reversed order of the table declarations--the result set will be the same, but it just make more sense to me.

    Code:
    select  CUST.BussinessName
            ,CON.Name
            ,C2C.Cust2ContID
            ,C2C.Cont2CustID
    from    dbo.Customers CUST
    inner
    join    (
            select  Cont2CustID
                    ,min(Cust2ContID) Cust2ContID
            from    dbo.Cust2Cont
            group
            by      Cont2CustID
            ) C2C on
                CUST.CustomerID=C2C.Cont2CustID
    inner
    join    dbo.Contacts CON on
                C2C.Cust2ContID=CON.ContactsID
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •