Professional Web Applications Themes

How use Subquery better ??? - Microsoft SQL / MS SQL Server

Hello, Someone knows how i can do a select query where I do match between rows ??? For Example, suppose that I have a Table like is: ** Table1 ** FA-FB-FC-FD ----------- 41-X-W-8 41-X-M-NULL 41-Y-N-10 Suppose that Customers is another table which I am going to apply the query. Select count(*) from Customers F Where F.CustFB in ( Select distinct FB from Table1 where FA = '41' ) and F.CustFC in ( Select distinct FC from Table1 where FA = '41' ) And ( F.CustFD like ( (Select FD from Table1 where FA = '41') + '%' ) ) -- ...

  1. #1

    Default How use Subquery better ???

    Hello,

    Someone knows how i can do a select query where
    I do match between rows ???

    For Example, suppose that I have a Table like is:

    ** Table1 **
    FA-FB-FC-FD
    -----------
    41-X-W-8
    41-X-M-NULL
    41-Y-N-10

    Suppose that Customers is another table which
    I am going to apply the query.


    Select count(*) from Customers F
    Where
    F.CustFB in
    (
    Select distinct FB from Table1 where FA = '41'
    )
    and
    F.CustFC in
    (
    Select distinct FC from Table1 where FA = '41'
    )
    And
    ( F.CustFD like ( (Select FD from Table1 where FA = '41')
    + '%' ) )

    -- Out error:
    Server: Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted
    when the subquery follows =, !=, <, <= , >, >= or when the
    subquery is used as an expression.

    Any idea? is possible use a joins how ?

    Any help is greatly appreciated.



    lubiel Guest

  2. #2

    Default Re: How use Subquery better ???

    To avoid the syntax error you can change the last condition in your WHERE
    clause like :

    AND EXISTS (SELECT *
    FROM Table1 t1
    WHERE t1.FA = '41'
    AND F.CustFD LIKE t1.FD + '%');
    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: How use Subquery better ???

    You have to post your DDLs with keys, constraints along with sample data &
    expected results. A brief explanantion of your requirements would be helpful
    too.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  2. Avoiding subquery
    By André Hänsel in forum MySQL
    Replies: 3
    Last Post: February 17th, 06:48 AM
  3. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  4. using list in subquery
    By spacehog in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: March 9th, 11:23 PM
  5. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 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