Solved

SQL Select statement

Posted on 2011-02-11
13
413 Views
Last Modified: 2012-05-11
Hi,

I have a table (TABLE_1)

I need a single select statement, to return matched records.


TABLE_1
-------------------------
ID      NAME      URL
1      A      A.COM
1      B      B.COM
NULL      A      A1.COM
NULL      B      B1.COM
NULL      C      C1.COM


For example

SQL: SELECT * FROM TABLE_1  WHERE ID = 1
Return
----------------------------------------------
ID      NAME            URL
1      A            A.COM
1      B            B.COM



SQL: SELECT * FROM TABLE_1  WHERE ID = 2

OR

SQL: SELECT * FROM TABLE_1  WHERE ID = 100

BOTH SELECT SHOULD RETURN (ID=2 is not in table_1)
----------------------------------------
ID      NAME            URL
NULL      A            A1.COM
NULL      B            B1.COM
NULL      C            C1.COM
0
Comment
Question by:gdupadhyay
  • 4
  • 2
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34872005
may be this (not understand your question)

SELECT * FROM TABLE_1  WHERE ID != 1
0
 
LVL 22

Expert Comment

by:Matt V
ID: 34872021
Are you trying to select IDs with no ID assigned?

how about

SELECT * FROM TABLE_1 WHERE ID = NULL;

or

SELECT * FROM TABLE_1 WHERE !ID;

0
 
LVL 9

Author Comment

by:gdupadhyay
ID: 34872053
It should return matched records otherwise ID=NULL records

If ID=1 (ID=1 is in table)
should return matched records

If ID = 3
should return only ID = NULL records, because ID=3 is not in table_1

I hope you understand my question
0
 
LVL 10

Expert Comment

by:GlobaLevel
ID: 34872093
--select all id records from both tables  where id = 1 for both both tables...
Select * from table_1 t1
JOIN Table2 t2 ON t1.id = t2.id
where t1.id = '1'
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34872127
in dbms Null means unavailable, unassigned, unknown so it should not equal to 3 or 4 or 100 or something else other than Null.

if your query returns no rows when ID = 3, that's according to said rules.
0
 
LVL 9

Author Comment

by:gdupadhyay
ID: 34872135
Please read my question. Its not a simple select statement.  I have only 1 table
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34872149
then you need this statement

return rows with id=? or if id=? doesn't exist then return rows with an id of null


select x.* from table_1 as x
where x.id=?
 or (x.id is null
              and not  exists (select id from table_1 as y where y.id=?)
     )
order by 1,2
0
 
LVL 22

Assisted Solution

by:Matt V
Matt V earned 100 total points
ID: 34872153
What you have is a programming logic question, not a query question.

You need your application to do a query, and if nothing is returned then re-issue the query to pull all NULL id records.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 150 total points
ID: 34872228
Mattymotas is correct ...

and it would depend on the frequency with which you assume that a not present id will be asked for as to whether it would be better to have the combined statement or two statements with a logic test between ...

either way the SQL code should go in a stored procedure and be executed within one...
that way your application still only makes one request to the database server,,,

but the optimal access solution can be (hopefully) provide in one call sequence.
0
 
LVL 10

Expert Comment

by:GlobaLevel
ID: 34872265
alos..where is this going...is it for a web app...I would as much on the client side...in  order to limit db calls...so for example if the id is somthing..then make the call to the db...that way if you have many users...you can filter them out on the client and save on resources on the db side...but I found your question to be very confusing...
0
 
LVL 9

Author Comment

by:gdupadhyay
ID: 34888513
Thanks for response.

I need to write stored procedure, what is the best way to write a stored procedure to fulfill above requirement?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 34891139
Use Lowfatspread's query and create a proc.
create procedure sp_Proc 
@var int as  
select x.* from table_1 as x
where x.id=@var
 or (x.id is null 
              and not  exists (select id from table_1 as y where y.id=@var)
     )
order by 1,2

Open in new window

Execute the proc by passing the variable.
exec sp_Proc @var = 100

Open in new window

0
 
LVL 9

Author Closing Comment

by:gdupadhyay
ID: 34940284
Thanks.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now