Professional Web Applications Themes

Select Statement Help Please - Microsoft SQL / MS SQL Server

Mike wrote:  Huh? How do we know which row in table B to use if the ID in table A is NULL? You need to rethink this design....

  1. #1

    Default Re: Select Statement Help Please

    Mike wrote: 
    Huh? How do we know which row in table B to use if the ID in table A is
    NULL? You need to rethink this design.


    Bob Guest

  2. #2

    Default Re: Select Statement Help Please

    Post DDL and DML!

    select
    *
    from tableC c
    left join tableA a on c.ID = a.ID
    left join tableB b on c.ID = b.ID
    where a.ID is not null
    or b.ID is not null

    --
    Dean Savovic
    www.teched.hr


    "Mike" <com> wrote in message
    news:08a701c34a11$3b93fe80$gbl... 


    deeeoo Guest

  3. #3

    Default Re: Select Statement Help Please

    Hi

    May be

    SELECT A.Value
    FROM TableA A JOIN TableC WHERE C.id = A.Id
    WHERE A.ID IS NOT NULL
    UNIONALL
    SELECT B.Value
    FROM TableB B JOIN TableC WHERE C.id = B.Id
    WHERE B.ID IS NOT NULL

    John

    "Mike" <com> wrote in message
    news:08a701c34a11$3b93fe80$gbl... 


    John Guest

  4. #4

    Default Re: Select Statement Help Please

    How about an expensive 3-way outer join working backwards from C:
    (assuming A & B have PKs apart from ID)

    select C.ID,
    isnull(A.PK, B.PK) as AB_ID
    from C
    left outer join A on C.ID = A.ID
    left outer join B on C.ID = B.ID
    where A.PK_ID = X or B.PK_ID = X

    (assumes always that either A.ID or B.ID is null)

    Depending on table sizes & indicies this could be very expensive. Be
    sure you test on production size data.

    If performance is an issue, the use a stored procedure if the DB
    supports it, or do it in code.

    - or re-design the problem if you can ;-)

    Charles

    "Mike" <com> wrote in message
    news:08a701c34a11$3b93fe80$gbl... 


    Charles Guest

  5. #5

    Default Select Statement Help Please

    Hi,

    This works

    SELECT * FROM TBLC WHERE IDVAL in

    (SELECT IDVAL FROM TBLA WHERE IDVAL<>'' UNION ALL SELECT
    IDVAL FROM TBLB WHERE IDVAL<>'')

    idval is the Col ID that is mapped to A,B,C

     
    kannan Guest

Similar Threads

  1. Support of sql-92 select statement
    By Fred in forum Informix
    Replies: 7
    Last Post: November 27th, 05:50 PM
  2. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  3. Select Distinct Statement Help
    By Ralph Freshour in forum PHP Development
    Replies: 5
    Last Post: August 20th, 07:27 AM
  4. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  5. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139