Professional Web Applications Themes

help on join in from clause - Microsoft SQL / MS SQL Server

Hi, I have a query 5 tables join as following: SELECT u.agent_name, u.telephone, u.email, a.address1, a.address2, a.city_name, a.state_cd, a.postal_cd, p.product_id, p.name, p.number, c.cc_type_cd, s.promote_rate FROM agent u, addresses a, cards c, product p LEFT JOIN sale_promote s on p.product_id = s.product_id WHERE u.address_id=a.address_id and u.agent_id=p.agent_id and p.product_id=c.product_id and c.active_flag='Y' and u.agent_id = ? and p.status_cd = ? AGENT can have multiple PRODUCT, AGENT has an ADDRESS. PRODUCT can have multiple CARD, some PRODUCT hava PROMOTE. the above query can only has the agent that actually has certain status's product. Now I would like to query out all the agent information ...

  1. #1

    Default help on join in from clause

    Hi,
    I have a query 5 tables join as following:
    SELECT u.agent_name, u.telephone, u.email,
    a.address1, a.address2, a.city_name, a.state_cd,
    a.postal_cd,
    p.product_id, p.name, p.number,
    c.cc_type_cd, s.promote_rate
    FROM agent u, addresses a, cards c,
    product p LEFT JOIN sale_promote s on p.product_id =
    s.product_id
    WHERE u.address_id=a.address_id and
    u.agent_id=p.agent_id and
    p.product_id=c.product_id and
    c.active_flag='Y' and
    u.agent_id = ? and
    p.status_cd = ?

    AGENT can have multiple PRODUCT, AGENT has an ADDRESS.
    PRODUCT can have multiple CARD, some PRODUCT hava PROMOTE.

    the above query can only has the agent that actually has
    certain status's product.

    Now I would like to query out all the agent information
    even the agent doesn't have the product. basicaly I would
    like to change the join of agent and product to be outer
    join. How can write it?

    Thanks
    Jen Guest

  2. #2

    Default help on join in from clause

    Not tested but try this:

    SELECT u.agent_name, u.telephone, u.email,
    a.address1, a.address2, a.city_name, a.state_cd,
    a.postal_cd,
    p.product_id, p.name, p.number,
    c.cc_type_cd, s.promote_rate
    FROM agent u join addresses a on
    u.address_id=a.address_id
    left outer join product p on u.agent_id=p.agent_id
    join cards c on p.product_id=c.product_id
    LEFT JOIN sale_promote s on p.product_id =
    s.product_id
    WHERE
    c.active_flag='Y' and
    u.agent_id = ? and
    p.status_cd = ?


    HTH

    Ray Higdon MCSE, MCDBA, CCNA
    >-----Original Message-----
    >Hi,
    >I have a query 5 tables join as following:
    >SELECT u.agent_name, u.telephone, u.email,
    > a.address1, a.address2, a.city_name, a.state_cd,
    >a.postal_cd,
    > p.product_id, p.name, p.number,
    > c.cc_type_cd, s.promote_rate
    > FROM agent u, addresses a, cards c,
    > product p LEFT JOIN sale_promote s on p.product_id =
    >s.product_id
    > WHERE u.address_id=a.address_id and
    > u.agent_id=p.agent_id and
    > p.product_id=c.product_id and
    > c.active_flag='Y' and
    > u.agent_id = ? and
    > p.status_cd = ?
    >
    >AGENT can have multiple PRODUCT, AGENT has an ADDRESS.
    >PRODUCT can have multiple CARD, some PRODUCT hava PROMOTE.
    >
    >the above query can only has the agent that actually has
    >certain status's product.
    >
    >Now I would like to query out all the agent information
    >even the agent doesn't have the product. basicaly I would
    >like to change the join of agent and product to be outer
    >join. How can write it?
    >
    >Thanks
    >.
    >
    Ray Higdon Guest

  3. #3

    Default Re: help on join in from clause

    Jen
    If you would posted some DDL with simple data It would be more easier to
    bring you the right solution
    Based on you descriptions here is my guess
    >Now I would like to query out all the agent information
    >even the agent doesn't have the product
    SELECT agent.col,agent.col,.... from agent LEFT JOIN product
    ON agent.PK=product.PK

    > >>AGENT has an ADDRESS.
    SELECT agent.col,agent.col,.... from agent INNER JOIN address
    ON address.PK=agent.PK
    > >>PRODUCT can have multiple CARD
    SELECT PRODUCT .col,PRODUCT .col,.... from PRODUCT LEFT JOIN CARD
    ON PRODUCT .PK=CARD.PK








    "Jen" <follower1999> wrote in message
    news:07cb01c34119$794f8bf0$a301280aphx.gbl...
    > No, it still can't get the agent without product.
    >
    > all the joins jamed together is kind of confusing, does
    > the join order matters? I think join in where clause is
    > much clear. where can I get information about this new sql-
    > 92 join? thanks
    >
    > >-----Original Message-----
    > >Not tested but try this:
    > >
    > >SELECT u.agent_name, u.telephone, u.email,
    > > a.address1, a.address2, a.city_name, a.state_cd,
    > >a.postal_cd,
    > > p.product_id, p.name, p.number,
    > > c.cc_type_cd, s.promote_rate
    > > FROM agent u join addresses a on
    > >u.address_id=a.address_id
    > > left outer join product p on u.agent_id=p.agent_id
    > > join cards c on p.product_id=c.product_id
    > > LEFT JOIN sale_promote s on p.product_id =
    > >s.product_id
    > > WHERE
    > > c.active_flag='Y' and
    > > u.agent_id = ? and
    > > p.status_cd = ?
    > >
    > >
    > >HTH
    > >
    > >Ray Higdon MCSE, MCDBA, CCNA
    > >
    > >>-----Original Message-----
    > >>Hi,
    > >>I have a query 5 tables join as following:
    > >>SELECT u.agent_name, u.telephone, u.email,
    > >> a.address1, a.address2, a.city_name, a.state_cd,
    > >>a.postal_cd,
    > >> p.product_id, p.name, p.number,
    > >> c.cc_type_cd, s.promote_rate
    > >> FROM agent u, addresses a, cards c,
    > >> product p LEFT JOIN sale_promote s on p.product_id
    > =
    > >>s.product_id
    > >> WHERE u.address_id=a.address_id and
    > >> u.agent_id=p.agent_id and
    > >> p.product_id=c.product_id and
    > >> c.active_flag='Y' and
    > >> u.agent_id = ? and
    > >> p.status_cd = ?
    > >>
    > >>AGENT can have multiple PRODUCT, AGENT has an ADDRESS.
    > >>PRODUCT can have multiple CARD, some PRODUCT hava
    > PROMOTE.
    > >>
    > >>the above query can only has the agent that actually has
    > >>certain status's product.
    > >>
    > >>Now I would like to query out all the agent information
    > >>even the agent doesn't have the product. basicaly I
    > would
    > >>like to change the join of agent and product to be outer
    > >>join. How can write it?
    > >>
    > >>Thanks
    > >>.
    > >>
    > >.
    > >

    Uri Dimant Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  3. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  4. Help - problem with sql where clause
    By man jackals in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 23rd, 11:19 AM

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