Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Does row exist

Posted on 2014-09-28
7
Medium Priority
?
276 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 111

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 111

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

916 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