Post reply

t-sql using either table-a ot table-b

  • October 23, 2008 at 11:03 am

    #77119

    Hi:

    Using t-sql, the program is required to use a key to read table-a and if there is no match then read table-b. The corresponding data for (table-a or tabls-b) is then to be printed.

    What is the best way to do this?

    Thanks in advance

    Balv

  • October 23, 2008 at 11:12 am

    #888760

    Left Joins and a coalesce:

    SELECT COALESCE(A.Value, B.Value)

    FROM Source

    LEFT JOIN TableA A ON Source.ID = A.ID

    LEFT JOIN TableB B ON Source.ID = B.ID

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 23, 2008 at 1:17 pm

    #888829

    or the non-set operation (not recommended, ha)

    SELECT data FROM A WHERE id = @id

    IF @@ROWCOUNT = 0

    SELECT data FROM B WHERE id = @id

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • October 23, 2008 at 1:43 pm

    #888847

    Thanks Seth & Jerry,

    The second option works better for me.

    Balv

  • October 23, 2008 at 8:53 pm

    #889004

    balv (10/23/2008)

    Thanks Seth & Jerry,

    The second option works better for me.

    Balv

    How does it work better for you? Why?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2008 at 10:55 am

    #889361

    The info required is found using a key and is located on table-a or table-b. The result gave the info on table-a (if available), else found the info on table-b. Which is close to the result that was envisaged.

    Now looking at part-2 of the problem (sproc is on database-a and the tables are database-b). This is due to that two users have their own databases. The second user requires the read access to a small portion of first users database.

  • October 24, 2008 at 11:55 am

    #889396

    balv (10/24/2008)

    The info required is found using a key and is located on table-a or table-b. The result gave the info on table-a (if available), else found the info on table-b. Which is close to the result that was envisaged.

    Yes, but the first answer does that also, and is in most respects better than the second answer. So why do you think that the second option fits your needs better than the first?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 24, 2008 at 12:01 pm

    #889401

    First option gave "Invalid object name 'Source'"

  • October 24, 2008 at 12:23 pm

    #889414

    OK, Garadin just made a small mistake, which is easily fixed:

    SELECT COALESCE(A.Value, B.Value)

    FROM TableA A ON Source.ID = A.ID

    FULL OUTER JOIN TableB B ON Source.ID = B.ID

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 24, 2008 at 12:34 pm

    #889423

    Thanks RBarryYoung:

    This has a problem " Incorrect syntax near the keyword 'ON'.".

  • October 24, 2008 at 12:42 pm

    #889427

    My original post assumed a starting table, which is what "Source" intended to represent. It wasn't intended to be literal ;).

    P.S. Barry, that Select is messed up.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 24, 2008 at 12:45 pm

    #889429

    Right, sorry:

    SELECT COALESCE(A.Value, B.Value)

    FROM TableA A

    FULL OUTER JOIN TableB B ON Source.ID = B.ID

    I tested the syntax this time 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 24, 2008 at 12:58 pm

    #889433

    Did you test that Source wasn't a table? :hehe:

    Do you mean this?

    SELECT COALESCE(A.ID, B.ID)

    FROM TableA A

    FULL OUTER JOIN TableB B ON A.ID = B.ID

    Or like me did you mean Source contextually. Either way, I'm interested in seeing how this works for this scenario, I haven't used full outer joins... ever =).

    Here's a few pre built table vars I made

    DECLARE @a TABLE(ID int, valvarchar(5))

    INSERT INTO @a(ID,val)

    SELECT 1,'A1' UNION ALL

    SELECT 2,'A2' UNION ALL

    SELECT 3,'A3'

    DECLARE @b-2 TABLE(ID int,val varchar(5))

    INSERT INTO @b-2(ID,val)

    SELECT 4,'B4' UNION ALL

    SELECT 5,'B5' UNION ALL

    SELECT 6,'B6'

    DECLARE @Source TABLE(ID int)

    INSERT INTO @Source

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 24, 2008 at 2:56 pm

    #889495

    Well, the syntax was fine, it's the semantics that were wrong... 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 24, 2008 at 5:14 pm

    #889559

    balv (10/24/2008)

    The info required is found using a key and is located on table-a or table-b. The result gave the info on table-a (if available), else found the info on table-b. Which is close to the result that was envisaged.

    Now looking at part-2 of the problem (sproc is on database-a and the tables are database-b). This is due to that two users have their own databases. The second user requires the read access to a small portion of first users database.

    Thanks for taking the time to explain. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply