Does row exist

I've been trying to figure out if/how its possible to check if a row exists in another table, but only if 'Removed' = null.

So for example:-
Contacts(Number, Name, Company, Office)
Features(ID, Number, Feature, added, removed)

What Im trying to do is something like:-
Select Company, Office, Number, iif((select count(*) from `features` where ((`Features'.`Number`=`Contacts`.`Number`) and (`Features`.`removed` = null) and (`Features`.`Feature`='Project Manager'))>1,true,false)

So the idea is to display all the `Feature` in the Features table that have `removed` as null, and return false, if it doesn't exist, but if the `feature`.`removed` isnt null then still return false.

My first thought was to try and do this as a PHP function, but this would involve multiple PHP query calls so hugely inefficient.

I hope this explains what Im trying to do enough for someone to point me in the right direction?
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
hugely inefficient
How many times per second do you need to run this (keeping in mind that there are 86,400 seconds in a day)?
0
Ray PaseurCommented:
Also, it would be helpful if you can post the CREATE TABLE statements and some test data so we can experiment with the scripts.  That way you can get tested-and-working code examples!
0
PortletPaulfreelancerCommented:
When you attempt to use NULL please never try using = or <>
instead, use:

IS NULL
IS NOT NULL

e.g. SELECT COUNT(*) FROM FEATURES WHERE REMOVED IS NULL
-----------------------------------------------------------------------------------------------
NULL is "special". It has no definite value and because of this it cannot be equal to something (not even itself)

so you have to use "special terms" such as IS NULL or IS NOT NULL
-----------------------------------------------------------------------------------------------

Now, for the remainder I don't understand

how its possible to check if a row exists in another table, but only if 'Removed' = null.
but if the `feature`.`removed` isnt null then still return false.
Those 2 lines mean that:
if any row exists in features for a contact you will get false
if no rows exist in features for a contact you will get true

& You do not mention why a Project Manager feature is important
-----------------------------------------------------------------------------------------------

But, please try this query, it might help
SELECT
      `Contacts`.Company
    , `Contacts`.Office
    , `Contacts`.Number
    , CASE WHEN IFNULL(F.count_of,0) = 0 THEN 'No Features' ELSE 'Has Features' END
    , IFNULL(F.count_of,0) feature_count
FROM `Contacts`
LEFT JOIN (SELECT `Number`, COUNT(*) as count_of
            FROM `Features` 
            GROUP BY `Number`
            ) AS F ON `Contacts`.`Number` = F.`Number`

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

tonelm54Author Commented:
Ok, sample data requested, so here is goes:-
CREATE TABLE IF NOT EXISTS `contracts` (
  `number` int(12) NOT NULL,
  `name` varchar(25) NOT NULL,
  `company` varchar(25) NOT NULL,
  `office` varchar(25) NOT NULL,
  PRIMARY KEY (`number`)
);

INSERT INTO `contracts` (`number`, `name`, `company`, `office`) VALUES
(125632, 'Dannette Dirksen', 'Sony', 'New York City'),
(561651, 'Siobhan Bongiovanni', 'Canon', 'Durban'),
(631932, 'Lelia Crume', 'Smirnoff', 'Belfast'),
(651456, 'Pasty Amante', 'Audi', 'New York City'),
(651698, 'Maisie Biles', 'Canon', 'Durban');

CREATE TABLE IF NOT EXISTS `features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `feature` int(11) NOT NULL,
  `added` int(11) NOT NULL,
  `removed` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `features` (`id`, `number`, `feature`, `added`, `removed`) VALUES
(1, 561651, 27, 45233, NULL),
(2, 561651, 27, 45233, 65211),
(3, 631932, 96, 12332, NULL),
(4, 651698, 27, 12355, 12355);

Open in new window


I think the query should be something like:-
SELECT
      `Contacts`.Company,
      `Contacts`.Office,
      `Contacts`.Number,
      iif ((Select count(*) FROM `features` where `features`.`feature`=27 and `features`.`number`= `Contacts`.`number` and `features`.`removed` <> null)>0,'Has Feature','No Feature') as 'Feature 27'
FROM `contacts`,
      iif ((Select count(*) FROM `features` where `features`.`feature`=32 and `features`.`number`= `Contacts`.`number` and `features`.`removed` <> null)>0,'Has Feature','No Feature') as 'Feature 32'
FROM `contacts`,
      iif ((Select count(*) FROM `features` where `features`.`feature`=96 and `features`.`number`= `Contacts`.`number` and `features`.`removed` <> null)>0,'Has Feature','No Feature') as 'Feature 96'
FROM `contacts`
LEFT JOIN `features` ON `contacts`.`number` = `features`.`number`

Open in new window


Which should give a result like:-
Test Table
0
tonelm54Author Commented:
The obvious thing to do is to replace 'has feature' with true, and 'no feature' with false but thought it best to stick to this for debugging purposes.

Thank you
0
PortletPaulfreelancerCommented:
|  COMPANY |        OFFICE | NUMBER |     COUNT_F |   COUNT_F27 |  COUNT_F32 |   COUNT_F96 |
|----------|---------------|--------|-------------|-------------|------------|-------------|
|     Sony | New York City | 125632 |  No Feature |  No Feature | No Feature |  No Feature |
|    Canon |        Durban | 561651 | Has Feature | Has Feature | No Feature |  No Feature |
| Smirnoff |       Belfast | 631932 | Has Feature |  No Feature | No Feature | Has Feature |
|     Audi | New York City | 651456 |  No Feature |  No Feature | No Feature |  No Feature |
|    Canon |        Durban | 651698 | Has Feature | Has Feature | No Feature |  No Feature |

Open in new window


Produced by the following query:
SELECT
      `contracts`.Company
    , `contracts`.Office
    , `contracts`.Number
    , CASE WHEN IFNULL(F.count_f,0)   = 0 THEN 'No Feature' ELSE 'Has Feature' END count_f
    , CASE WHEN IFNULL(F.count_f27,0) = 0 THEN 'No Feature' ELSE 'Has Feature' END count_f27
    , CASE WHEN IFNULL(F.count_f32,0) = 0 THEN 'No Feature' ELSE 'Has Feature' END count_f32
    , CASE WHEN IFNULL(F.count_f96,0) = 0 THEN 'No Feature' ELSE 'Has Feature' END count_f96
FROM `contracts`
LEFT JOIN (SELECT `Number`
                , COUNT(*) as count_f
                , COUNT(case when `feature` = 27 then 1 end) as count_f27
                , COUNT(case when `feature` = 32 then 1 end) as count_f32
                , COUNT(case when `feature` = 96 then 1 end) as count_f96
            FROM `Features` 
            GROUP BY `Number`
            ) AS F ON `contracts`.`Number` = F.`Number`
;

Open in new window

also see:  http://sqlfiddle.com/#!9/53971/8

{+ edit}
I have grouped the features table so that we get the counts calculated AND we don't only get one row per contract, if you just join the features to the contracts without grouping you would get more than one row per contract.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonelm54Author Commented:
Excellent, thank you, even better I that its a working example :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.