SQL Question

bringitdown

Hi

Not a SQL expert, but am trying to formulate a query as follows:

Table 1:
<UNIQUE KEY1>|<DATA1>|<DATA2>

Table 2:
<UNIQUE KEY2>|<FOREIGN KEY1>|<DATA3>

Data example:

134|Misc|Random
135|Misc|Random

2|134|Something
3|134|Something else
4|134|EXCLUDEME
5|135|Nothing

Now I want to return all unique RIGHT JOIN on these tables but ONLY when an instance of <DATA 3> for that key does not contain a particular value eg. EXCLUDEME.

e.g. so the query on above data set would return 135 but exclude 134.

Apologies if its not clear. I can do it in code but was wondering if there is an SQL shortcut as its a pain keeping track querying for every <UNIQUE KEY1>.

kayos

Code for SQL Server based on what I could understand from your post

DECLARE @Tbl1 TABLE
(
	ID INT,
	Data1 VARCHAR(10),
	Data2 VARCHAR(10)
)
DECLARE @Tbl2 TABLE
(
	ID INT,
	FKID INT,
	Data3 VARCHAR(30)
)

INSERT
	@tbl1
SELECT
	134,'misc','random'
UNION
SELECT
	135,'misc','random'

INSERT
	@tbl2
SELECT
2,134,'Something'
UNION 
SELECT
3,134,'Something else'
UNION 
SELECT
4,134,'EXCLUDEME'
UNION 
SELECT
5,135,'Nothing'

SELECT
	tbl1.*
FROM
	@tbl1 AS tbl1
	JOIN
		(
		SELECT
			FKID
		FROM 
			@tbl2 AS Tbl2
		WHERE
			tbl2.data3 = 'excludeme'
		) AS D ON (tbl1.ID != d.FKID)

bringitdown

Cheers will give it a blast .. am using MySQL, querying a Bugzilla database for some statistics ... its been ages since I did SQL in any detail.

Ginkgo

This might help

SELECT
*
FROM
@tbl1 AS tbl1
JOIN
(
SELECT
*
FROM
@tbl2 AS Tbl2
WHERE
not EXISTS
(select FKID from @tbl2 as Tbl3 where Tbl2.FKID = Tbl3.FKID and Tbl3.DATA3 ='excludeme')
)AS D ON(tbl1.ID = d.FKID)

bonkey

bringitdown wrote: »

Table 1:
<UNIQUE KEY1>|<DATA1>|<DATA2>

Table 2:
<UNIQUE KEY2>|<FOREIGN KEY1>|<DATA3>

Data example:

134|Misc|Random
135|Misc|Random

2|134|Something
3|134|Something else
4|134|EXCLUDEME
5|135|Nothing

Now I want to return all unique RIGHT JOIN on these tables but ONLY when an instance of <DATA 3> for that key does not contain a particular value eg. EXCLUDEME.

e.g. so the query on above data set would return 135 but exclude 134.

I'm gonna use LEFT JOINS to answer this - you can swap one for the other, and I think better with LEFT JOINs.

The trick is this... the ON clause of a JOIN is applied before join occurs. The WHERE clause is applied after the join occurs. So...

SELECT DISTINCT a.*, b.*
FROM table_1 a
LEFT JOIN table_2 b
ON a.Unique_Key_1 = b.Foreign_Key_1
WHERE b.DATA3 <> 'EXCLUDEME'

if you don't want all fields, then change the select, and the DISTINCT will make it unique.
If you really, really have to use a RIGHT JOIN, then this is a homework assignment and I've still left you with something to do


WTA: I've only noticed you're using MySQL. I can't guarantee that it honours the principle that "ON" occurs before the join, and "WHERE" occurs after. Oracle, MSSQL, DB2 all do.