Question

  • Creator
    Topic
  • #2226466

    SQL IQ Test

    Locked

    by kinhuei ·

    There are 2 tables, each table contains the records as below:
    Table_1 -> Column_1 : A A A B B C C C D E E E E F F G G G
    Table_2 -> Column_2 : A B

    u hv to retrieve the records by SQL Script shown as : C D E F G (without A & B, based on Table_2)
    **Rule: there is non-redundant record

    Try to use SQL Script to test this Question….hope u all can give me the answer, bcoz it is asked by my fren….thx!

All Answers

  • Author
    Replies
    • #2636335

      Clarifications

      by kinhuei ·

      In reply to SQL IQ Test

      Clarifications

    • #2636196

      I’d do a

      by dr dij ·

      In reply to SQL IQ Test

      regular (inner join) in crystal
      then have it show only the records where ‘isnul’ on the first table.

      I know you actually should just change the join type. But I like to see what records are present for auditing purposes. It would of course return records faster if done on server by changing join type

    • #2635933

      Try this

      by gcamilleri ·

      In reply to SQL IQ Test

      SELECT
      Table_1.COLUMN_1
      FROM
      Table_1 LEFT OUTER JOIN Table_2
      ON
      Table_1.COLUMN_1 = Table_2.COLUMN_2
      WHERE
      Table_2.COLUMN_2 IS NULL
      GROUP BY
      Table_1.COLUMN_1

    • #2635930

      two possibilities

      by robert.mcmurray ·

      In reply to SQL IQ Test

      I would use the IN() subquery to exclude records, it is simpler to read the TSQL. However both seem to cost the same CPU time.
      Be careful with large tables.

      — create temp tables
      DECLARE @t1 TABLE (col_a NCHAR)
      DECLARE @t2 TABLE (col_b NCHAR)

      INSERT INTO @T1 (col_a) VALUES (‘A’)
      INSERT INTO @T1 (col_a) VALUES (‘A’)
      INSERT INTO @T1 (col_a) VALUES (‘A’)
      INSERT INTO @T1 (col_a) VALUES (‘B’)
      INSERT INTO @T1 (col_a) VALUES (‘B’)
      INSERT INTO @T1 (col_a) VALUES (‘C’)
      INSERT INTO @T1 (col_a) VALUES (‘C’)
      INSERT INTO @T1 (col_a) VALUES (‘C’)
      INSERT INTO @T1 (col_a) VALUES (‘D’)
      INSERT INTO @T1 (col_a) VALUES (‘E’)
      INSERT INTO @T1 (col_a) VALUES (‘E’)
      INSERT INTO @T1 (col_a) VALUES (‘E’)
      INSERT INTO @T1 (col_a) VALUES (‘E’)
      INSERT INTO @T1 (col_a) VALUES (‘F’)
      INSERT INTO @T1 (col_a) VALUES (‘F’)
      INSERT INTO @T1 (col_a) VALUES (‘G’)
      INSERT INTO @T1 (col_a) VALUES (‘G’)
      INSERT INTO @T1 (col_a) VALUES (‘G’)

      INSERT INTO @T2 (col_b) VALUES (‘A’)
      INSERT INTO @T2 (col_b) VALUES (‘B’)

      –simple version to read
      SELECT DISTINCT col_a FROM @t1 WHERE col_a NOT IN (SELECT col_b FROM @t2)

      — more complex
      SELECT DISTINCT col_a FROM @t1 LEFT OUTER JOIN @t2 ON col_a = col_b WHERE col_b IS NULL

    • #2635907

      Use the above qry

      by ghaneshwart ·

      In reply to SQL IQ Test

      SELECT DISTINCT(Column_1) from Table_1 where
      NAME NOT IN (SELECT Column_2 FROM Table_2)

    • #2620203

      SQL IQ Test

      by minhas_ir ·

      In reply to SQL IQ Test

      select distinct column_1 from testtable_1 t1
      where column_1 not in (select column_2 from testtable_2)

    • #2620045

      SQL IQ Test

      by balachandras ·

      In reply to SQL IQ Test

      SELECT Column_1 FROM Table_1
      WHERE Column_1 NOT IN
      (SELECT Column_2 FROM Table_2)
      GROUP BY Column_1

    • #2618682

      2 solutions: using outer joins or subqueries

      by sorbelloale ·

      In reply to SQL IQ Test

      1st:

      select Column_1
      from Table_1 left join Table_2
      on Column_1 = Column_2
      where Column_2 is null

      2nd:

      select Column_1
      from Table_1
      where Column_1 not in
      (select Column_2 from Table_2)

      Both queries will have the same output:

      Column_1
      ——–
      C
      C
      C
      D
      E
      E
      E
      E
      F
      F
      G
      G
      G

      If you dont want to have repeated entries, just use the “distinct” statement at the beginning of the query.

Viewing 7 reply threads