Home Forums SQL Server 2008 T-SQL (SS2K8) Exists on where clause RE: Exists on where clause

  • February 21, 2017 at 2:55 pm

    #1929624

    KGJ-Dev - Thursday, February 16, 2017 1:47 PM

    Hi,

    Sample data to play with

    Declare @vendor table(IdVenor int,MessageId int);
    Declare @vendor_customer table(IdVenorCustom int,MessageId int);
    Declare @Message table(MessageId int, description varchar(4000));

    insert into @Message(MessageId,description)
    select 100,'sample1' union all
    select 200,'sample2' union all
    select 200,'sample3' union all
    select 400,'sample4' union all
    select 500,'sample4'

    insert into @vendor(IdVenor,MessageId)
    select 1,100 union all
    select 1,200 union all
    select 1,300 union all
    select 1,400

    insert into @vendor_customer(IdVenorCustom,MessageId)
    select 1,100 union all
    select 1,200 union all
    select 1,300 union all
    select 1,400 union all
    select 1,500

    select * from @Message M where (exists(select 1 from @vendor where MessageId = M.MessageId ) or
    exists(select 1 from @vendor_customer where MessageId = M.MessageId ) )

    The logic i am looking for is, if any data exists in @vendor just bring those else bring from @vendor_custom. since i am using or condition it passes both the condition. 
    Any suggestion how to fix this?
    [Note:  i can write if else condition to achieve this.]. But i need to fix this with my above query. Any suggestion please

    Expected result :  

    Since data exists from @vendor table, i need to get those results.

    MessageId    description
    100    sample1
    200    sample2
    200    sample3
    400    sample4

    Canyou take the time to learn how to post valid DDL and how RDBMS works?Tables model sets; therefore there names have to be plural orcollective nouns. Since you do not do math on identifiers, they cannever be integers. Basically your posting a linked list from the 1950assembly language programs, but you are using SQL to do it.

    Didyou know that by definition, a table has to have a key? But there isno way your samples can have a key. Why are not you using the correctsyntax for the insertion statements?

    Ihave seen some of your other postings. And frankly, I would neveremploy you or let you graduate from a class of mine. You really donot understand what is going on.

    CREATETABLE Vendors

    (vendor_idCHAR(10) NOT NULL PRIMARY KEY);

    CREATETABLE Something_Messages

    (message_nbrINTEGER NOT NULL PRIMARY KEY,

    message_txtNVARCHAR(4000) NOT NULL);

    CREATETABLE Customer_Messages

    (vendor_idCHAR(10) NOT NULL

    REFERENCESVendors (vendor_id)

    ONDELETE CASCADE,

    message_nbrINTEGER NOT NULL

    REFERENCESSomething_Messages(message_nbr)

    ONDELETE CASCADE,

    PRIMARYKEY (vendor_id, message_nbr)

    );

    Whydid you use the old Sybase syntax for your insertion statement? Itwas replaced decades ago by the row constructor syntax which isANSI/ISO standard?

    >>The logic I am looking for is, if any data exists in Vendors justbring those else bring from Vendors_custom. since I am using orcondition it passes both the condition. <<

    thanksto referential integrity, this request makes no sense at all. Yourcode is more than a decade out of date.

    Please post DDL and follow ANSI/ISO standards when asking for help.