[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

SQL Select statement

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
gdupadhyay
Asked:
gdupadhyay
  • 4
  • 2
  • 2
  • +3
3 Solutions
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
may be this (not understand your question)

SELECT * FROM TABLE_1  WHERE ID != 1
0
 
Matt VCommented:
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
 
gdupadhyayAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GlobaLevelCommented:
--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
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
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
 
gdupadhyayAuthor Commented:
Please read my question. Its not a simple select statement.  I have only 1 table
0
 
LowfatspreadCommented:
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
 
Matt VCommented:
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
 
LowfatspreadCommented:
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
 
GlobaLevelCommented:
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
 
gdupadhyayAuthor Commented:
Thanks for response.

I need to write stored procedure, what is the best way to write a stored procedure to fulfill above requirement?
0
 
SharathData EngineerCommented:
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
 
gdupadhyayAuthor Commented:
Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now