Professional Web Applications Themes

Join problem in a View - Microsoft SQL / MS SQL Server

I have 2 tables: FILES and ACTIONS I would like all the Files to get listed with all their Actions when the actions are on a certain date. So I have something like: select * from files left outer join actions on AC_FIID = FI_ID where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL)) Anyone know why I don't get the files listed that don't have any actions connected to them? This has me puzzled for the last 3 hours :( I think it has to do with the where clause but if I can't use a where on the Actions ...

  1. #1

    Default Join problem in a View

    I have 2 tables: FILES and ACTIONS

    I would like all the Files to get listed with all their Actions when the
    actions are on a certain date.

    So I have something like:

    select * from files left outer join actions on AC_FIID = FI_ID
    where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL))

    Anyone know why I don't get the files listed that don't have any actions
    connected to them?

    This has me puzzled for the last 3 hours :( I think it has to do with the
    where clause but if I can't use a where on the Actions table then how can I
    make do this query?

    Thanks in advance!!!


    Stijn Verrept Guest

  2. #2

    Default Re: Join problem in a View

    1) make sure ac_stamp values are really null they are not spaces
    2) ac_stamp column is part of the table actions.(if you are trying to find
    out rows with null values from the right hand side table.)

    See the following example on northwind database.
    --NULL values will be returned for non matching rows for the right side
    table of LEFT OUTER JOIN.

    select a.customerid,b.customerid
    from customers1 a left outer join orders b
    on a.customerid = b.customerid
    where a.customerid is null
    order by 1
    --above query will not return any rows.

    select a.customerid,b.customerid
    from customers1 a left outer join orders b
    on a.customerid = b.customerid
    where b.customerid is null
    order by 1

    --above query will return rows.

    --post some sample rows and table structures for accurate query.

    --
    -Vishal
    "Stijn Verrept" <sverreptnospan.vub.ac.be> wrote in message
    news:OwtsUxHRDHA.1560TK2MSFTNGP12.phx.gbl...
    > I have 2 tables: FILES and ACTIONS
    >
    > I would like all the Files to get listed with all their Actions when the
    > actions are on a certain date.
    >
    > So I have something like:
    >
    > select * from files left outer join actions on AC_FIID = FI_ID
    > where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL))
    >
    > Anyone know why I don't get the files listed that don't have any actions
    > connected to them?
    >
    > This has me puzzled for the last 3 hours :( I think it has to do with the
    > where clause but if I can't use a where on the Actions table then how can
    I
    > make do this query?
    >
    > Thanks in advance!!!
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Join problem in a View

    > Anyone know why I don't get the files listed that don't have any actions
    > connected to them?
    It works for me (with assumed DDL):

    CREATE TABLE files (fi_id INTEGER PRIMARY KEY)
    CREATE TABLE actions (ac_fiid INTEGER REFERENCES files (fi_id), ac_stamp
    DATETIME, PRIMARY KEY (ac_fiid,ac_stamp))

    INSERT INTO files VALUES (1)

    SELECT * FROM
    files LEFT OUTER JOIN actions ON AC_FIID = FI_ID
    WHERE ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL))

    Result:

    fi_id ac_fiid ac_stamp
    ----------- ----------- ----------------------------------------------------
    --
    1 NULL NULL


    Try also:

    SELECT *
    FROM files
    LEFT OUTER JOIN actions
    ON AC_FIID = FI_ID AND AC_Stamp >= '2003-06-30'

    which gives the same result. If that doesn't work, post DDL and sample data
    (as above) so that others can reproduce the problem.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Stijn Verrept" <sverreptnospan.vub.ac.be> wrote in message
    news:OwtsUxHRDHA.1560TK2MSFTNGP12.phx.gbl...
    > I have 2 tables: FILES and ACTIONS
    >
    > I would like all the Files to get listed with all their Actions when the
    > actions are on a certain date.
    >
    > So I have something like:
    >
    > select * from files left outer join actions on AC_FIID = FI_ID
    > where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL))
    >
    > Anyone know why I don't get the files listed that don't have any actions
    > connected to them?
    >
    > This has me puzzled for the last 3 hours :( I think it has to do with the
    > where clause but if I can't use a where on the Actions table then how can
    I
    > make do this query?
    >
    > Thanks in advance!!!
    >
    >

    David Portas Guest

  4. #4

    Default Re: Join problem in a View

    Stijn,

    You need to add the AC_Stamp predicate to the ON clause, (the second
    query in David's post), and remove it from the WHERE clause. Of course,
    then you no longer need the "OR (AC_Stamp IS NULL)" part.

    With the current query, you will filter out all the files that have
    actions < '2003-06-30'.

    When using a LEFT JOIN, you should put all predicates with respect to
    the 'right' table in the ON clause, otherwise filtering will occur.

    Hope this helps,
    Gert-Jan


    Stijn Verrept wrote:
    >
    > I have 2 tables: FILES and ACTIONS
    >
    > I would like all the Files to get listed with all their Actions when the
    > actions are on a certain date.
    >
    > So I have something like:
    >
    > select * from files left outer join actions on AC_FIID = FI_ID
    > where ((AC_Stamp >= '2003-06-30') OR (AC_Stamp IS NULL))
    >
    > Anyone know why I don't get the files listed that don't have any actions
    > connected to them?
    >
    > This has me puzzled for the last 3 hours :( I think it has to do with the
    > where clause but if I can't use a where on the Actions table then how can I
    > make do this query?
    >
    > Thanks in advance!!!
    Gert-Jan Strik Guest

  5. #5

    Default Re: Join problem in a View

    Thanks everyone for their replies, I'm getting back to the customer on
    thursday to test things out :)

    "Gert-Jan Strik" <sorrytoomuchspamalready.nl> wrote in message
    news:3F09C8F2.5006680Atoomuchspamalready.nl...
    > Stijn,
    >
    > You need to add the AC_Stamp predicate to the ON clause, (the second
    > query in David's post), and remove it from the WHERE clause. Of course,
    > then you no longer need the "OR (AC_Stamp IS NULL)" part.
    >
    > With the current query, you will filter out all the files that have
    > actions < '2003-06-30'.
    >
    > When using a LEFT JOIN, you should put all predicates with respect to
    > the 'right' table in the ON clause, otherwise filtering will occur.
    >
    > Hope this helps,
    > Gert-Jan

    Stijn Verrept Guest

  6. #6

    Default Re: Join problem in a View

    Works great now, thanks everyone.

    "Stijn Verrept" <sverreptnospan.vub.ac.be> wrote in message
    news:uP4ZE2SRDHA.2148TK2MSFTNGP10.phx.gbl...
    > Thanks everyone for their replies, I'm getting back to the customer on
    > thursday to test things out :)
    >
    > "Gert-Jan Strik" <sorrytoomuchspamalready.nl> wrote in message
    > news:3F09C8F2.5006680Atoomuchspamalready.nl...
    > > Stijn,
    > >
    > > You need to add the AC_Stamp predicate to the ON clause, (the second
    > > query in David's post), and remove it from the WHERE clause. Of course,
    > > then you no longer need the "OR (AC_Stamp IS NULL)" part.
    > >
    > > With the current query, you will filter out all the files that have
    > > actions < '2003-06-30'.
    > >
    > > When using a LEFT JOIN, you should put all predicates with respect to
    > > the 'right' table in the ON clause, otherwise filtering will occur.
    > >
    > > Hope this helps,
    > > Gert-Jan
    >
    >

    Stijn Verrept Guest

  7. #7

    Default Join problem in a view

    Dear friends,
    I am not familiar with self join or other join. If anybody can
    give me an example to figure out this question, it would be
    greatly appreciated.

    I have a table Item(Item_No, Item_Name). Now how
    do I create a view with sequentially incrementing field "INDEX_"
    by self join or other join?

    Data:
    Item_No, Item_Name
    111, aaaa
    222, bbbb
    333, cccc
    444, dddd

    Result:
    INDEX_, Item_No, Item_Name
    1, 111, aaaa
    2, 222, bbbb
    3, 333, cccc
    4, 444, dddd

    Cheers,
    Vincent






    Vincent Guest

  8. #8

    Default Re: Join problem in a view

    select INDEX_ = (select count(*) from Item i2 where i2.Item_No <=
    i.Item_No),
    Item_No,
    Item_Name
    from Item i
    order by Item_No

    Nigel Rivett (SQL Server MVP)
    www.nigelrivett.net

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Nigel Guest

  9. #9

    Default Re: Join problem in a view

    Hi Nigel and other friends,
    Thanks for your reply. Besdies this, Is it possible to get the result
    by alternate method and how? I am learning MSSQL syntax and hope
    somebody gives me some suggestions. Because I can't find it
    from myself or my SQL books.

    Result:
    INDEX_, Item_No, Item_Name
    1, 111, aaaa
    2, 222, bbbb
    3, 333, cccc
    4, 444, dddd


    "Nigel Rivett" <com> wrote in message
    news:#$phx.gbl... 


    Vincent Guest

Similar Threads

  1. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 6th, 09:19 PM
  2. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 6th, 06:09 PM
  3. Join problem
    By Lasse Edsvik in forum ASP Database
    Replies: 20
    Last Post: January 7th, 08:19 PM
  4. Inner Join problem
    By in forum ASP Database
    Replies: 7
    Last Post: December 11th, 06:31 PM
  5. SQL JOIN Problem !!! Could you help me please ???
    By François in forum PHP Development
    Replies: 9
    Last Post: November 29th, 04:14 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