Solved

Does row exist

Posted on 2014-09-28
7
255 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 48

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 48

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to close my php post page. 3 42
What does "Between" mean? 6 48
integrated two Separate file . 3 30
Allow the whole div to submit form not just button 2 14
This article discusses four methods for overlaying images in a container on a web page
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

752 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