Link to home
Create AccountLog in
Avatar of gdupadhyay
gdupadhyayFlag for United States of America

asked on

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
Avatar of Muhammad Ahmad Imran
Muhammad Ahmad Imran
Flag of United Kingdom of Great Britain and Northern Ireland image

may be this (not understand your question)

SELECT * FROM TABLE_1  WHERE ID != 1
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;

Avatar of gdupadhyay

ASKER

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
--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'
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.
Please read my question. Its not a simple select statement.  I have only 1 table
Avatar of Lowfatspread
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
SOLUTION
Avatar of Matt V
Matt V
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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...
Thanks for response.

I need to write stored procedure, what is the best way to write a stored procedure to fulfill above requirement?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks.