INTERSECT, JOIN, Query problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INTERSECT, JOIN, Query problem

Author

Topic

Zanph
Starting Member

12 Posts

Posted - 2008-08-01 : 03:24:18

Hi,

I'm on SQL 2000, and I need to come up with a query to produce the following.

I have three tables, gzdaten, gz_zusatzattribut and gz_zusatzattributdef. Each gzdaten (land, place, here the projects stand) and gz_zusatzattribut has an unique key, the gz_zusatzattribut and gz_zusatzattributdef (this table extend the gz_zusatzattribut) also have a unique key in. Each gzdaten can have multiple rows in the gz_zusatzattribut table.

If I do this

SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM
gzdaten, gz_zusatzattribut, gz_zusatzattributdef
WHERE
gz_zusatzattribut.GZID = gzdaten.GZID AND
gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID AND
gzdaten.GZPRE = "16" AND
gzdaten.GZ = "07018"
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME;

I get a result set of persons that is a member of any of those three groups . No problem here.

But if I want to return persons that are members in just, e.g. gz_zusatzattributdef = "8" and gz_zusatzattributdef = "5", i will get no results. SQL 2000 does not have the INTERSECT clause that might solve this.

Any ideas ??

Philipp

pootle_flump

1064 Posts

Posted - 2008-08-01 : 04:08:51

Hi

1) You would do well to use ANSI join syntax - it is much easier to read and understand.
2) I am not certain what the question is: do you mean return all people where EITHER gz_zusatzattributdef = "8" OR gz_zusatzattributdef = "5"?

Zanph
Starting Member

12 Posts

Posted - 2008-08-01 : 04:27:15

Hi pootle_flump

1) You would do well to use ANSI join syntax - it is much easier to read and understand.

thanks for the information to use JOINS, i actually thought it needs more working power.

2) I am not certain what the question is: do you mean return all people where EITHER gz_zusatzattributdef = "8" OR gz_zusatzattributdef = "5"?

Yeah exactly this i mean, if i write gz_zusatzattributdef = "8" OR gz_zusatzattributdef = "5" then i get thousands of results (gz_zusatzattribut has 25000 entries) .

But i will explain it again in a better way:
SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM
gzdaten, gz_zusatzattribut, gz_zusatzattributdef
WHERE
gz_zusatzattribut.GZID = gzdaten.GZID AND
gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID AND
gzdaten.GZPRE = "16" AND
gz_zusatzattributdef.ATTRIBUTID IN (5,8) //i don't want to get all results where 5 and 8 is, i just want to get the result were projekts have both attributids, 5 and 8
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

I hope you understand now my problem a little lit better
Philipp

pootle_flump

1064 Posts

Posted - 2008-08-01 : 04:41:23

You could use a derived table:

.....
INNER JOIN
	(SELECT GZID 
	FROM	gz_zusatzattributdef
	WHERE	gz_zusatzattributdef.ATTRIBUTID IN (5,8)
	GROUP BY GZID
	HAVING	COUNT(*) = 2) AS gzid_58
ON	gzid_58.GZID = gzdaten.GZID
.....

This assumes that (GZID, ATTRIBUTID) is unique.

Another reason for ANSI joins - the old style joins make it much easier to introduce a bug (accidental cross join).

Zanph
Starting Member

12 Posts

Posted - 2008-08-01 : 08:18:45

Sorry for my stupidness^^ but I don't know how to combine your solution with the previous code. Here I tried it, can you find the problem?

Thank you
Philipp

SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM
gz_zusatzattribut, gzdaten
INNER JOIN gz_zusatzattributdef
(SELECT GZID
FROM gz_zusatzattributdef
WHERE gz_zusatzattributdef.ATTRIBUTID IN (5,8)
GROUP BY GZID
HAVING COUNT(*) = 2) AS gzid_58
ON gzid_58.GZID = gzdaten.GZID
WHERE
gz_zusatzattribut.GZID = gzdaten.GZID AND
gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID AND
gzdaten.GZPRE = "16" AND
gz_zusatzattributdef.ATTRIBUTID IN (5,8)
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

Zanph
Starting Member

12 Posts

Posted - 2008-08-04 : 03:27:41

Can you please help me again because i need it in my summerjob

Thank you
Philipp

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 03:33:20

were you just struggling with the join syntax?


SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM gz_zusatzattribut 
 join gzdaten on gz_zusatzattribut.GZID = gzdaten.GZID
 join gz_zusatzattributdef on gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID
 join	(SELECT GZID 
	FROM gz_zusatzattributdef
	WHERE gz_zusatzattributdef.ATTRIBUTID IN (5,8)
	GROUP BY GZID
	HAVING COUNT(*) = 2) AS gzid_58
    on gzid_58.GZID = gzdaten.GZID
WHERE gzdaten.GZPRE = '16' 
      AND gz_zusatzattributdef.ATTRIBUTID IN (5,8) 
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

Zanph
Starting Member

12 Posts

Posted - 2008-08-04 : 03:59:35

This alert appears:
Unknown column 'GZID' in 'field list'

if I try to write
join (SELECT gzdaten.GZID
FROM gz_zusatzattributdef
WHERE gz_zusatzattributdef.ATTRIBUTID IN (5,8)
GROUP BY GZID
HAVING COUNT(*) = 2) AS gzid_58

it also doesn't work

Philipp

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 04:07:45

i just rewrote your query with ansi joins.... so GZID is not in your gz_zusatzattributdef table? what is then?

Em

Zanph
Starting Member

12 Posts

Posted - 2008-08-04 : 04:12:54

ok cheap mistake^^ (GZID stands in gzdaten)

but it finds all projects were the 5 or 8 is included but i just want to find the projects were 5 and 8 is included, so both numbers has to stand in the project

I have to say that a project can have more than one gz_zusatzattribut (thats the big problem)!


SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM gz_zusatzattribut 
 join gzdaten on gz_zusatzattribut.GZID = gzdaten.GZID
 join gz_zusatzattributdef on gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID
 join	(SELECT gzdaten.GZID 
	FROM gz_zusatzattributdef, gzdaten
	WHERE gz_zusatzattributdef.ATTRIBUTID IN (5,8)
	GROUP BY gzdaten.GZID
	HAVING COUNT(*) = 2) AS gzid_58
    on gzid_58.GZID = gzdaten.GZID
WHERE gzdaten.GZPRE = '16' 
      AND gz_zusatzattributdef.ATTRIBUTID IN (5,8) 
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

Philipp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 14:01:55

quote:
Originally posted by Zanph

ok cheap mistake^^ (GZID stands in gzdaten)

but it finds all projects were the 5 or 8 is included but i just want to find the projects were 5 and 8 is included, so both numbers has to stand in the project

I have to say that a project can have more than one gz_zusatzattribut (thats the big problem)!

SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM gz_zusatzattribut
join gzdaten on gz_zusatzattribut.GZID = gzdaten.GZID
join gz_zusatzattributdef on gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID
join (SELECT gzdaten.GZID
FROM gz_zusatzattributdef, gzdaten
WHERE gz_zusatzattributdef.ATTRIBUTID IN (5,8)
GROUP BY gzdaten.GZID
HAVING COUNT(DISTINCT gz_zusatzattributdef.ATTRIBUTID) = 2) AS gzid_58
on gzid_58.GZID = gzdaten.GZID
WHERE gzdaten.GZPRE = '16'
AND gz_zusatzattributdef.ATTRIBUTID IN (5,8)
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

Philipp

just modify like above and see if gives you desired result

Zanph
Starting Member

12 Posts

Posted - 2008-08-05 : 04:02:54

Thank you for the answer but it also gives me the same result. I tried it to change it a little bit but i get the same result (in fact also a little bit slower). The difference is that gz_zusatzattributdev is replaced with gz_zusatzattribut because in gz_zusatzattribut (n) are all the entries in n:1 to gz_daten(1). In gz_zusatzattributdev is just an extension of the gz_zusatzattribut.
For example:
gz_zusatzattribut


ATTID GZID ATTRIBUTID
1     12      8             
2     12      5
25000 8200    8

gz_zusatzattributdev


ATTRIBUTID ATTRIBUTNAME
1          Project is active
5          Project is finnished
8          For the Project exists an invoice

gz_daten


GZID NAME        LAND      BEGIN
8200 Project50  Austria    01-01-2008
12   Project12  Germany    05-05-2008
12   Project12  Germany    07-07-2008


SELECT
gzdaten.GZ,
gzdaten.BEZEICHNUNG,
gz_zusatzattributdef.*,
gz_zusatzattribut.*
FROM gz_zusatzattribut 
 join gzdaten on gz_zusatzattribut.GZID = gzdaten.GZID
 join gz_zusatzattributdef on gz_zusatzattribut.ATTRIBUTID = gz_zusatzattributdef.ATTRIBUTID
 join	(SELECT gzdaten.GZID 
	FROM gz_zusatzattribut, gzdaten
	WHERE gz_zusatzattribut.ATTRIBUTID IN (5,8)
	GROUP BY gzdaten.GZID
	HAVING COUNT(DISTINCT gz_zusatzattribut.ATTRIBUTID) = 2) AS gzid_58
    on gzid_58.GZID = gzdaten.GZID
WHERE gzdaten.GZPRE = '16' 
      AND gz_zusatzattributdef.ATTRIBUTID IN (5,8) 
ORDER BY gzdaten.BEZEICHNUNG, gz_zusatzattributdef.ATTRIBUTNAME

Thank you for your commitment
Philipp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources