Click here to Skip to main content
15,877,857 members
Please Sign up or sign in to vote.
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
Updated 12-Feb-18 8:21am
v2
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?

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
 
Share this answer
 
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 ''
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.
 
Share this answer
 
Comments
[no name] 12-Feb-18 11:34am    
They would come from in('1','2','3') etc
Wendelius 12-Feb-18 11:43am    
And you want to have a row in the result set if the number isn't found? If that is the case you need a row which would be listed in the result hence you need a table (or function) containing the numbers to test.
[no name] 12-Feb-18 11:53am    
I've updated the question with my current code
Wendelius 12-Feb-18 12:50pm    
In your updated code, you forgot the FROM clause from the correlated subquery


...
FROM DataTable b
...


The subquery should query the actual table where you want to find the numbers
Maciej Los 12-Feb-18 14:13pm    
5ed!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900