Solved

Does row exist

Posted on 2014-09-28
7
261 Views
Last Modified: 2014-09-29
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?
0
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40348966
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40348967
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40349101
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:tonelm54
ID: 40349352
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
 

Author Comment

by:tonelm54
ID: 40349353
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40349362
|  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
 

Author Closing Comment

by:tonelm54
ID: 40349422
Excellent, thank you, even better I that its a working example :-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question