Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    11

    Exclamation Unanswered: SQL to search exist or not exist data

    Hello all,

    I have a branch a data, now i need to search through a database to check whther it is exist in that database or not, any syggestion?
    Example:

    Now i have data 123, 234, 345. Let say data 123 and 234 is exist in that database, but data 345 is not exist in that database.

    What SQL am i suitable to use to get those result exist(123, 234) and those result not exist (345)?

    The database structure is someting like this:
    ID NAME O_NAME
    -- ----- --------
    1 120 123
    2 234 234
    3 345 345

    1) The data consider 'exist' if it exist in column NAME or column O_NAME (either one).

    2) The data consider 'not exist' if it not exist in neither NAME column nor O_NAME column

    After the SQL query, someting is expected:

    Exist:

    ID NAME O_NAME
    -- ----- --------
    1 120 123
    2 234 234

    Not Exist:

    ID NAME O_NAME
    -- ----- --------
    3 345 345

    I'll appreciate if anyone can provide me a solution..Thanks!

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    I am not clear what exactly u want.
    see this sample code
    [code]
    use pubs
    go
    create table #t
    (ID int, NAME varchar(20), O_NAME varchar(20)
    )
    -- ----- --------
    insert into #t
    select 1, '120', '123' union
    select 2, '234', '234' union
    select 3, '345', '345' UNION
    select 4, '345', '346'
    ------------select--------
    select * from #t where NAME<>'345' and O_NAME<>'345'

    select * from #t where NAME='345' or O_NAME='345'
    [code]
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Aug 2005
    Posts
    11
    Hi thanks for reply!

    But i think the sql query is able to show me those exist in database only, let says data '345' is exist, so it will show the '345' in result, but if 345 not exist, it will show whole result in database which is not data '345'.

    Anyway, thanks for giving the solution, it's give me some hint for the sql query, i hope will accomplish the result soon.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    use pubs
    go
    create table #t
    	(ID int,
    	NAME varchar(20),
    	O_NAME varchar(20))
    -- ----- --------
    insert into #t
    select 1, '120', '123' union
    select 2, '234', '234' union
    select 3, '345', '345' UNION
    select 4, '345', '346'
    ------------select--------
    --EXISTS query:
    select	*
    from	#t
    where	NAME='345'
    	or O_NAME='345'
    
    --NOT EXISTS query
    select	*
    from	#t
    where	ID not in
    	(select	ID
    	from	#t
    	where	NAME='345'
    		or O_NAME='345')
    
    --Alternate NOT EXISTS:
    select	*
    from	#t
    	left outer join --ExistsIDs
    		(select	ID
    		from	#t
    		where	NAME='345'
    			or O_NAME='345')  ExistsIDs
    		on #t.ID = ExistsIDs.ID
    where	ExistsIDs.ID is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •