Results 1 to 4 of 4

Thread: Help with SQL

  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Smile Unanswered: Help with SQL

    HiHi~

    I am trying to select the ProdNo and the OrdNo from the table OrdLine where OrdNo contains only one ProdNo

    SELECT OrdLine.OrdNo, OrdLine.ProdNo FROM OrdLine

    Result:

    ORDNO PRODNO
    -------- --------
    O1579999 P1556678
    O1579999 P6677900
    O1656777 P1556678
    O2334661 P1556678
    O3331222 P1556678
    O3331222 P3455443
    O5511365 P1556678
    O5511365 P3455443
    O5511365 P6677900
    O7847172 P1556678
    O7847172 P6677900
    O7959898 P1556678
    O7959898 P3455443
    O7959898 P6677900

    The problem is, I can't seem to remove the duplicateds ~
    The final result should be ~


    ORDNO PRODNO
    -------- --------
    O1656777 P1556678
    O2334661 P1556678

    What should I add to my SQL statment??

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    select o.ordno, o.prodno
    from ordline o
    where o.ordno in (select o1.ordno from ordline o1
                      group by o1.ordno
                      having count(distinct o1.prodno) = 1
                     );

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Or with analytics :

    Code:
    select ordno, prodno
    from (select o.ordno, o.prodno, 
             count(distinct o.prodno) over (partition by o.ordno) as cnt
          from ordline o
          )
    where cnt = 1;

  4. #4
    Join Date
    Mar 2004
    Posts
    17

    Smile

    Thanks so much ~~~

    I have combined your SQL with the one I made up myself ~

    Code:
    SELECT OrdNo, OrdDate FROM OrderTbl WHERE OrdNo IN (
    	SELECT OrdLine.OrdNo FROM OrdLine WHERE OrdLine.ProdNo IN (
    		SELECT Product.ProdNo FROM Product WHERE Product.ProdName LIKE '%Ink Jet%'
    	)
    	GROUP BY OrdLine.OrdNo HAVING COUNT(DISTINCT OrdLine.ProdNo) = 1
    );
    I know the SQL and the result are different than yours, but it fulfill my needs.
    Took me at least 3 hours to come up with this SQL ~


    I will be posting another question soon ~ If you experts can help me out with another one, that would be great ~~

Posting Permissions

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