Subquery WHERE IN or EXISTS 3

[southbean]

Hello All,

I’ve not used SQL in quite some time and am having a little trouble remembering how to do this (if I even knew in the first place).

What I need is the column records in query #1 where the DISTINCT criteria in query #2 is true.

I’ve tried a UNION, WHERE IN or EXISTS queries but with no success.

Can anyone please help me out here?

Many thanks for any/all suggestions!

- tm

Query #1

SELECT 
	t1.unit, t1.dept, t1.code, t1.month, t1.year
FROM 
	tbl1 t1
WHERE 
	t1.unit>'500' AND t1.dept>'600'

Query #2

SELECT DISTINCT
	t2.unit, t2.dept, t2.code
FROM 
	tbl2 t2
WHERE 
	t2.unit>'500' AND t2.dept>'600'

 

[bborissov]

Try

SELECT
    t1.unit, t1.dept, t1.code, t1.month, t1.year
FROM tbl1 t1
INNER JOIN tbl2 t2
      ON t1.unit = t2.unit AND
         t1.dept = t2.dept AND
         t1.code = t2.code AND
         t2.unit > '500'    AND
         t2.dept > '600'
WHERE t1.unit>'500' AND t1.dept>'600'

I wonder why you use strings for numeric values, but this is your choice.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[southbean]

Borislav,

Thank you very much for your reply.
I must apologize for an error in my previous post. It changes my question entirely.

I am using the SAME table in both queries. Therefore, what I need is the columns in query #1 where the DISTINCT criteria in query #2 is true.

Thanks again!

- Tom

Query #2

SELECT DISTINCT
    t2.unit, t2.dept, t2.code
FROM 
    tbl1 t2
WHERE 
    t2.unit>'500' AND t2.dept>'600'

 

[Lamprey13]

Here is one way that should work:

SELECT 
    t1.unit, t1.dept, t1.code, t1.month, t1.year
FROM 
    tbl1 t1
INNER JOIN
	(
		SELECT DISTINCT
			t2.unit, t2.dept, t2.code
		FROM 
			tbl2 t2
		WHERE 
			t2.unit>'500' AND t2.dept>'600'
	) AS Temp
	ON t1.unit = Temp.Unit
	AND t1.dept = Temp.dept
	AND t1.code = Temp.code
WHERE 
    t1.unit>'500' AND t1.dept>'600'

NOTE: THe last WHERE clause may not be needed, but it may help query performance.

 

[ESquared]

Taking a wild guess at what you're looking for and stealing code from Lamprey13 for speed's sake:

SELECT 
    t1.unit, t1.dept, t1.code, t1.month, t1.year
FROM 
    tbl1 t1
INNER JOIN
    (
        SELECT DISTINCT
            t2.unit, t2.dept, t2.code
        FROM 
            tbl2 t2
        WHERE 
            t2.unit>'500' AND t2.dept>'600'
[blue]        GROUP BY
           t2.unit, t2.dept, t2.code
        HAVING
           Count(*) >= 2[/blue]
    ) AS Temp
    ON t1.unit = Temp.Unit
    AND t1.dept = Temp.dept
    AND t1.code = Temp.code
WHERE 
    t1.unit>'500' AND t1.dept>'600'

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[bborissov]

The DISTINCT criteria is ALWAYS true if you have at least ONE record that match your condition. If you want to get these values that have more than one record in the table ESquared gave you the solution.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[southbean]

Lamprey13, ESquared & bborissov

Thank you all for your posts! ESquared's tweak to Lamprey13 code proved to be the one that worked for me.

Again, thank you all very much for your help! It was invaluable!!!

- tm