Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

SQL IQ Test

Locked

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!

Topic

Clarifications

In reply to SQL IQ Test

Clarifications

I’d do a

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

Try this

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

two possibilities

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

Use the above qry

In reply to SQL IQ Test

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

SQL IQ Test

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)

SQL IQ Test

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

2 solutions: using outer joins or subqueries

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.

[Author]

Replies