If multiple records exist in a table SQL

Question

0.00/5 (No votes)

See more:

Hi,

I'm trying to output results in the following format;

number exists
123456 1
345633 0
243234 1
865445 1

where 'exists' is 1 or 0 depending on whether 'number' exists in table X.

I've tried all sorts of things, but it always ends up only showing the numbers which exist alongside 1, but not showing the ones which don't exist alongside 0.

I'd like to do this without creating additional tables if possible, and it's important to return whether each field exists or not as soon as it's found in X, as X contains multiple hundred thousand records and it would be inefficient if the record is found but the rest of the table is scanned to continue looking for it.

UPDATE:

I now have:

CREATE TEMPORARY TABLE numbers
(number int(11));

insert into numbers (number) values
(1),(2),(3);

SELECT n.number
COALESCE((SELECT TOP 1
        1
where n.number in ('1','2','3','4')), 0) AS 'exists'
FROM numbers n

but now there's yet another syntax error,

Error in query (1064): Syntax error near '((SELECT TOP 1 1 where n.number in ('1','2','3','4')), 0) AS 'exists' ' at line 2

What I have tried:

SELECT 1 WHERE EXISTS
INNER JOIN
UNION

Posted 12-Feb-18 5:08am

User 13204940

Updated 12-Feb-18 8:21am

v2

Add a Solution

Comments

F-ES Sitecore 12-Feb-18 11:23am

Not sure why you think someone can give you a working query without knowing the database schema or some sample data. Regardless you probably want to "LEFT JOIN" the two tables together and do some kind of "CASE WHEN" that returns 1 is a field in the table you've left joined is "not null" and 0 if it "is null".

[no name] 12-Feb-18 11:25am

They can use common sense to know how this works, neither the asker nor the answerer need to spoonfeed.

F-ES Sitecore 12-Feb-18 11:29am

You clearly need to be spoon fed as you were given an example implementation of my comment above and you don't understand what bit of the solution is the bit you actually need and what bit is just setting up sample data that you don't need. It's why there is little point spending effort of these types of questions as the asker rarely has the ability to actually understand what they're being told and apply it to their own problem.

[no name] 12-Feb-18 11:31am

I know exactly which part is example data, that's obvious. Don't take me for an idiot.

F-ES Sitecore 12-Feb-18 11:38am

So why did you respond complaining about the set up of the sample data rather than look at the meat of the query, understanding it, and applying it to your own data?

[no name] 12-Feb-18 11:40am

I'm not complaining about sample data, creating a temporary table has nothing to do with my implementation. I thought you would have known that.

F-ES Sitecore 12-Feb-18 11:43am

Yes you are, the bit of syntax you said wasn't working isn't needed for your solution, it is simply setting up test data.

[no name] 12-Feb-18 11:44am

Without creating persistent tables, that's how I'm looking to do it, but it doesn't work and that has nothing to do with my implementation.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Answer 1 · 2018-02-12T05:20:00

Solution 1

You can try something like

SQL

DECLARE @tableY TABLE (Number INT)

DECLARE @tableX TABLE (Number INT)

INSERT INTO @tableY
	SELECT 123456  UNION
	SELECT 345633   UNION
	SELECT 243234   UNION
	SELECT 865445   

INSERT INTO @tableX
	SELECT 345633   UNION
	SELECT 243234   UNION
	SELECT 865445   

SELECT Y.Number, 
CASE 
	WHEN ISNULL(x.Number,'') = '' THEN 0
	ELSE 1
END 'Exists'
FROM @tableY y
LEFT JOIN @tableX x
ON y.Number = x.Number

Output:

Number	Exists
123456	0
243234	1
345633	1
865445	1

Posted 12-Feb-18 5:20am

Bryian Tan

Comments

[no name] 12-Feb-18 11:24am

I get

Error in query (1064): Syntax error near 'DECLARE @tableY TABLE (Number INT) DECLARE @tableX TABLE (Number INT) INSE' at line 1

Bryian Tan 12-Feb-18 15:24pm

are you using Microsoft SQL server? or Oracle database?

[no name] 12-Feb-18 11:51am

I've updated the question with my current code

Maciej Los 12-Feb-18 14:13pm

5ed!

Jörgen Andersson 12-Feb-18 15:26pm

You should probably exchange WHEN ISNULL(x.Number,'') = '' THEN 0 for WHEN x.Number IS NULL THEN 0
It saves one operation and a cast and is probably more correct in the different case where both tables actually contains ''

Wendelius · Answer 2 · 2018-02-12T05:33:00

You wrote that you'd like to do this without creating additional tables if possible. Where would the numbers come from if you don't list them in a table or similar?

What comes to the query itself, it can be done in multiple ways. Personally I would perhaps prefer the following format

SQL

SELECT a.TestNumber,
       COALESCE( (SELECT TOP 1
               1
        FROM DataTable b
        WHERE b.Number = a.TestNumber), 0) AS Exists
FROM NumberToTestTable a

The query above should prevent multiplication of rows in case the number exists several times in the data table. Also TOP 1 structure should end the investigation as soon as a match is found.

From the performance point of view, it would be critical to index the Number column in the DataTable in order to efficiently scan for the data.