Question

Locked

SQL IQ Test

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!

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

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

Collapse -

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

Collapse -

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

Collapse -

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)

Collapse -

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)

Collapse -

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

Collapse -

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.

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums