Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Sep 2017
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Multiple Criterias are true ?

    Hi All,

    I am trying to select all solicitors that offer X service,

    However, some of these solicitors have certain criteria attached. For example we will take X case, but not if it is over 18 months old. I have written the following query, but it does not work correctly. I am not sure I have written the table properly to accommodate this. I am somewhat an amateur.
    PHP Code:
    SELECT
      Solicitors
    .*,
      
    ServicesOffered.*,
      
    ServiceTypes.ServiceName,
      
    RTACriteriaList.RTACriteriaListName
    FROM Solicitors
      INNER JOIN ServicesOffered
        ON Solicitors
    .SolicitorID ServicesOffered.SolicitorID
      INNER JOIN ServiceTypes
        ON ServicesOffered
    .ServiceTypeID ServiceTypes.ServiceTypeID
      INNER JOIN RTACriteria
        ON Solicitors
    .SolicitorID RTACriteria.SolicitorID
      INNER JOIN RTACriteriaList
        ON RTACriteria
    .RTACriteriaListID RTACriteriaList.RTACriteriaListID
    WHERE ServiceTypes
    .ServiceTypeID 9
    AND RTACriteria.RTACriteriaListID 1
    AND RTACriteria.Setting >= 19
    AND RTACriteria.RTACriteriaListID 5
    AND RTACriteria.Setting 'No' 
    Any help would be appreciated.

    EDIT: There are multiple Solicitors, who have multiple servies they over, and multiple Criteria for each service. I think this is where im going wrong.

    EDIT 2: Not sure of the ebst way to include data so I have included a table dump.

    So the Query is basically a search query. I need to list all the solicitors that will for example take an RTA case on, which has a limitation date of 19 months. These varibles will change, but in this example, it should list only one solicitor, called FHF as the other solicitor in the table, will only take it up to 18 months.

    Thanks again.

    EDIT 3:

    Sorry, me again, the expected result would be to list all the solicitors as above. In teh example above only one solicitor would return, and we would expect the folling coloums to be returned.

    SolicitorID, SolicitorName, LogoURL, Description, ServiceTypeID, Fee, ServiceName, RTACriteriaListName

    However, as the website grows, multiple solicitors would be retuned.

    EDIT 4: After further testing, the query runs as expected when I only specify one criteria.

    PHP Code:
    AND RTACriteria.RTACriteriaListID 1
    AND RTACriteria.Setting >= 19 
    However this only returns solicitors that would accept the case in the required months. When I add:

    PHP Code:
    AND RTACriteria.RTACriteriaListID 5
    AND RTACriteria.Setting 'No' 
    The query returns 0 results. But as you can see by the below data, there are results to return. How can I get around this issue?

    PHP Code:
    --
    -- 
    Table structure for table `RTACriteria`
    --

    CREATE TABLE `RTACriteria` (
      `
    RTACriteriaIDint(11NOT NULL,
      `
    SolicitorIDint(11) DEFAULT NULL,
      `
    RTACriteriaListIDint(11) DEFAULT NULL,
      `
    Settingvarchar(255) DEFAULT NULL
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- 
    Dumping data for table `RTACriteria`
    --

    INSERT INTO `RTACriteria` (`RTACriteriaID`, `SolicitorID`, `RTACriteriaListID`, `Setting`) VALUES
    (111'18'),
    (
    215'No'),
    (
    325'Yes'),
    (
    421'24');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `RTACriteriaList`
    --

    CREATE TABLE `RTACriteriaList` (
      `
    RTACriteriaListIDint(11NOT NULL,
      `
    RTACriteriaListNamevarchar(255) DEFAULT NULL
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- 
    Dumping data for table `RTACriteriaList`
    --

    INSERT INTO `RTACriteriaList` (`RTACriteriaListID`, `RTACriteriaListName`) VALUES
    (1'Limitation'),
    (
    2'Medical Attention'),
    (
    3'Damages'),
    (
    4'Minors'),
    (
    5'Roundabouts');

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `ServicesOffered`
    --

    CREATE TABLE `ServicesOffered` (
      `
    ServicesOfferedIDint(11NOT NULL,
      `
    SolicitorIDint(11) DEFAULT NULL,
      `
    ServiceTypeIDint(11) DEFAULT NULL,
      `
    Feeint(5) DEFAULT NULL
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- 
    Dumping data for table `ServicesOffered`
    --

    INSERT INTO `ServicesOffered` (`ServicesOfferedID`, `SolicitorID`, `ServiceTypeID`, `Fee`) VALUES
    (119600),
    (
    217400),
    (
    313300),
    (
    427300),
    (
    522300),
    (
    629600);

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `ServiceTypes`
    --

    CREATE TABLE `ServiceTypes` (
      `
    ServiceTypeIDint(11NOT NULL,
      `
    ServiceNamevarchar(50) DEFAULT NULL,
      `
    CategoryIDint(11) DEFAULT NULL
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

    --
    -- 
    Dumping data for table `ServiceTypes`
    --

    INSERT INTO `ServiceTypes` (`ServiceTypeID`, `ServiceName`, `CategoryID`) VALUES
    (1'Accidents at Work'NULL),
    (
    2'Trips & Slips'NULL),
    (
    3'Serious Injury Claims'NULL),
    (
    4'Motorcycle Accidents'NULL),
    (
    5'Holiday Claims'NULL),
    (
    6'Defective Product Claims'NULL),
    (
    7'Criminal Injuries'NULL),
    (
    8'Noise Induced Hearing Loss'NULL),
    (
    9'Road Traffic Accidents'NULL),
    (
    10'Unfair Dismissal / Discrimination At Work'NULL),
    (
    11'PPI Claims'NULL),
    (
    12'Recovering Care Home Fees'NULL);

    -- --------------------------------------------------------

    --
    -- 
    Table structure for table `Solicitors`
    --

    CREATE TABLE `Solicitors` (
      `
    SolicitorIDint(11NOT NULL,
      `
    SolicitorNamevarchar(50) DEFAULT NULL,
      `
    LogoURLvarchar(255) DEFAULT NULL,
      `
    Descriptionvarchar(255) DEFAULT NULL
    ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of Solicitors';

    --
    -- 
    Dumping data for table `Solicitors`
    --

    INSERT INTO `Solicitors` (`SolicitorID`, `SolicitorName`, `LogoURL`, `Description`) VALUES
    (1'Jeff Solicitors''sollogo.jpg''Been in the industry for 7 years'),
    (
    2'FHF Solicitors''FHFsologo.jpg''Specialise in medical cases'); 

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,032
    Thanks
    3
    Thanked 490 Times in 479 Posts
    The query will match row(s) that result in a TRUE value for the WHERE clause.

    Since any particular column in a row cannot be two different values at the same time, your logic with ... RTACriteriaListID = 1 AND ... RTACriteriaListID = 5 will always be false.

    To return a SET (you remember set theory from Algebra) of rows, you need to OR the pairs of conditions -

    Code:
    (RTACriteria.RTACriteriaListID = 1 AND RTACriteria.Setting >= 19) OR (RTACriteria.RTACriteriaListID = 5 AND RTACriteria.Setting = 'No')
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. Users who have thanked CFMaBiSmAd for this post:

    Anthony2oo5 (09-14-2017)

  4. #3
    New to the CF scene
    Join Date
    Sep 2017
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi CFMaBiSmAd, Thanks very much for your reply. I thought this might be where I was going wrong.

    The thing is I need both to be true. An OR will only return one or the other. And I also have other Criteria that I need may need to put in. I have designed the database incorrectly in order to do this? Is there any way around it? maybe subqueries?

    Thanks again

  5. #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,032
    Thanks
    3
    Thanked 490 Times in 479 Posts
    The thing is I need both to be true.
    You would use GROUP BY some_common_id and HAVING COUNT(*) = 2 in the sql query.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  6. Users who have thanked CFMaBiSmAd for this post:

    Anthony2oo5 (09-14-2017)

  7. #5
    New to the CF scene
    Join Date
    Sep 2017
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi, and thanks again for taking the time to respond to me. Im still lost, I have been trying to implement what you wrote for the past few hours, with a lot more Google searching, but had no luck.

    I think my main problem is I have conditions that I may need to apply to the query.

    PHP Code:
    INSERT INTO `RTACriteriaList` (`RTACriteriaListID`, `RTACriteriaListName`) VALUES 
    (1'Limitation'), 
    (
    2'Medical Attention'), 
    (
    3'Damages'), 
    (
    4'Minors'), 
    (
    5'Roundabouts'); 
    Each one of those would be;

    Where Limitation = No
    AND
    Where Medical Attention = Yes
    AND
    Where Minors = Yes

    thats just an example. is there anyway for me to add multiple conditions ? Or have I designed the database structure incorrectly?

    Do you offer freelance work?

  8. #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,032
    Thanks
    3
    Thanked 490 Times in 479 Posts
    Upon closer review of the information, you will need to have a JOIN for each Criteria and use a different alias name for each, such as c1, c2.

    You should in fact be using alias names now, to de-clutter the query.

    The following should (untested) work, at least for the JOIN and WHERE conditions (you will need to add things like cl1.RTACriteriaListName c1_name, cl2.RTACriteriaListName c2_name to the SELECT term to get the individual values) -

    Code:
    SELECT 
      Solicitors.*, 
      ServicesOffered.*, 
      ServiceTypes.ServiceName, 
      RTACriteriaList.RTACriteriaListName 
    FROM Solicitors 
      INNER JOIN ServicesOffered 
        ON Solicitors.SolicitorID = ServicesOffered.SolicitorID 
      INNER JOIN ServiceTypes 
        ON ServicesOffered.ServiceTypeID = ServiceTypes.ServiceTypeID 
      INNER JOIN RTACriteria c1
        ON Solicitors.SolicitorID = c1.SolicitorID 
      INNER JOIN RTACriteriaList cl1
        ON c1.RTACriteriaListID = cl1.RTACriteriaListID 
      INNER JOIN RTACriteria c2
        ON Solicitors.SolicitorID = c2.SolicitorID 
      INNER JOIN RTACriteriaList cl2
        ON c2.RTACriteriaListID = cl2.RTACriteriaListID 
    WHERE ServiceTypes.ServiceTypeID = 9 
    AND c1.RTACriteriaListID = 1 
    AND c1.Setting >= 19 
    AND c2.RTACriteriaListID = 5 
    AND c2.Setting = 'No'
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  9. Users who have thanked CFMaBiSmAd for this post:

    Anthony2oo5 (09-14-2017)

  10. #7
    New to the CF scene
    Join Date
    Sep 2017
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi again,

    I think i know what you mean now and that looks like the path I need to take.

    However I got the following error from PHPMyAdmin,

    #1054 - Unknown column 'RTACriteriaList.RTACriteriaListName' in 'field list'

    I think if you could help me with this I may be good on my own

    Thanks very much so far, much appreciated.

  11. #8
    New to the CF scene
    Join Date
    Sep 2017
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I got it working. You are the man, thanks very much.


 

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
  •