Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Does row exist

Posted on 2014-09-28
7
Medium Priority
?
272 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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