Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    6

    Red face Unanswered: Creating a complicated View

    Hi Guys,

    I want to create a view from multiple tables. This is where it gets complicated!


    The tables are as follows: -

    Table 1 - Client
    Table 2 - Apps A
    Table 3 - Apps B
    Table 4 - Apps C

    The view that I wish to create is a list of all clients and all appointments that they have received.

    When an appointment is created, in any of the App Tables the Client ID (from client table) is assigned to the appointment. This is the common field.

    Ideally I want to create a view that lists the clients and the number of appointments that they have had.

    How can this be done?

    Best,

    Jon

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Something like this?
    Code:
    CREATE VIEW MyView AS
    (SELECT Client.Id,
    	Client.Name,
    	(SELECT COUNT(*) FROM AppsA WHERE Client.Id = AppsA.ClientId) as NrAppsA_Appointments,
    	(SELECT COUNT(*) FROM AppsB WHERE Client.Id = AppsB.ClientId) as NrAppsB_Appointments,
    	(SELECT COUNT(*) FROM AppsC WHERE Client.Id = AppsC.ClientId) as NrAppsC_Appointments
    )
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2011
    Posts
    6

    Another Quesion

    Hi Wim,

    Thanks for this, it has worked a treat! See code below

    Code:
    SELECT DISTINCT
    ework.Affiliate_Sales_Management.fld_id,
    ework.Affiliate_Sales_Management.fld_ifa_firm_name,
     
    (SELECT COUNT(*) FROM ework.Consumer_Management WHERE ework.Affiliate_Sales_Management.fld_id = ework.Consumer_Management.cli_id AND ework.Consumer_Management.con_status LIKE 'COMPLETE') AS Consumer_Appointments,
    (SELECT COUNT(*) FROM ework.Commercial_Opportunity WHERE ework.Affiliate_Sales_Management.fld_id = ework.Commercial_Opportunity.cli_id AND ework.Commercial_Opportunity.com_status LIKE 'COMPLETE') AS Commercial_Appointments,
    (SELECT COUNT(*) FROM ework.Professional_Opportunity WHERE ework.Affiliate_Sales_Management.fld_id = ework.Professional_Opportunity.cli_id AND ework.Professional_Opportunity.pro_status LIKE 'COMPLETE') AS Professional_Appointments
     
    FROM
    ework.Affiliate_Sales_Management
    The only improvement I want to carry out is that every appointment has a certain status - i.e. Complete, Re-booked etc. As you may see from the code above, I have tried to implement this by adding the following line of code to each select count statement:

    Code:
    AND ework.Consumer_Management.con_status LIKE 'COMPLETE'
    Unfortunately, this brings back incorrect data (I have checked the original tables).

    Any ideas? Sorry, quit new to SQL and still getting my head round some of the logic.

    Best,

    JMC

Tags for this Thread

Posting Permissions

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