7 Replies - 748 Views - Last Post: 12 November 2008 - 02:47 PM Rate Topic: -----

#1 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

The good results, only 2 times too many?!

Posted 11 November 2008 - 11:54 AM

Hello, I have the following problem:

I have 1 table ("Verzonden_Tekeningen") which is filled with data (column-id's) from 3 other tables: "Tekeningen", "Datums", "Status".

Now i wanna get back the data from these 3 tables. The data to get is defined through the in the table "Verzonden_Tekeningen" because this tables holds the saved column-id's.

I have the following code:

	declare @BedrID as int = '46'
	declare @Werknummer as varchar(20) = 'E0000'
	
	select t4.Verzonden_naar, t4.Werknummer, t4.Bladnummer, t4.Naam, t4.Datum, t5.StatusNaam from
	
	(select t2.Verzonden_naar, t2.Werknummer, t2.Bladnummer, t2.Naam, t3.datumid, t3.Datum from
	
	 (select t.Werknummer, t.Bladnummer, t.Naam, t.TekeningID, v.Verzonden_naar from Tekeningen t inner join
	   (select * from Verzonden_Tekeningen2) v on t.TekeningID=v.TekeningID) t2
	  
		inner join 
		
	   (select Datums.Datum, Datums.TekeningID, Datums.DatumID from datums inner join (select * from Verzonden_Tekeningen2) v2 on Datums.DatumID = v2.DatumID) t3
		 on t2.TekeningID=t3.TekeningID) t4
		 
		  inner join 
		  
			(select Status.StatusNaam, Status.DatumID from Status inner join (select * from Verzonden_Tekeningen2) v5 on Status.StatusID=v5.StatusID) t5
			  on t4.DatumID=t5.DatumID
			  
			   where t4.Werknummer=@Werknummer and t4.verzonden_naar=@BedrID


This code gets the right code, this is not problem, the only problem is i get all results in triple:

Verzonden_naar	Werknummer	Bladnummer	Naam	Datum	StatusNaam
46	E0000	P02	Funderingsoverzicht	2008-12-01 20:45:00	Voor Uitvoering
46	E0000	P02	Funderingsoverzicht	2008-12-01 20:45:00	Voor Uitvoering
46	E0000	P02	Funderingsoverzicht	2008-12-01 20:45:00	Voor Uitvoering
46	E0000	P02	Funderingsoverzicht	2008-12-01 20:45:00	Voor Uitvoering


Why do i get the results 3 times?

PLEASE HELP!!

Is This A Good Question/Topic? 0
  • +

Replies To: The good results, only 2 times too many?!

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: The good results, only 2 times too many?!

Posted 11 November 2008 - 12:34 PM

i seriously doubt you need that many subqueries.

Make sure that every table that you need to use is encounterend only once in the query, and that they are all joined together using well formed inner joins that join them on useful fields.
You should end up with ONE select statement.
Was This Post Helpful? 0
  • +
  • -

#3 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: The good results, only 2 times too many?!

Posted 11 November 2008 - 01:47 PM

I indeed have too many select-statements. I'm close to the solution. thnx for the support!
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: The good results, only 2 times too many?!

Posted 11 November 2008 - 01:59 PM

care to provide a table layout of the three(or four?) tables? that would help out.
Was This Post Helpful? 0
  • +
  • -

#5 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: The good results, only 2 times too many?!

Posted 11 November 2008 - 02:36 PM

If you look at all of the columns the query is returning, I'm sure you will see why you are getting multiple results. One simple solution would be
select DISTINCT x, y, z, from

Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: The good results, only 2 times too many?!

Posted 12 November 2008 - 12:14 PM

Stay away from poxy distinct!
If your select statement is returning too many results, fix the join, dont expect distinct to fix it.
Distinct stinks, not even bouncing titties can prevent that.
Was This Post Helpful? 0
  • +
  • -

#7 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: The good results, only 2 times too many?!

Posted 12 November 2008 - 01:19 PM

You also use the table Verzonden_Tekeningen2, what is that for?
Was This Post Helpful? 0
  • +
  • -

#8 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: The good results, only 2 times too many?!

Posted 12 November 2008 - 02:47 PM

View PostTrogdor, on 12 Nov, 2008 - 11:14 AM, said:

Stay away from poxy distinct!
If your select statement is returning too many results, fix the join, dont expect distinct to fix it.
Distinct stinks, not even bouncing titties can prevent that.


I agree Trogdor, distinct is not required if you set up your joins properly. However, it would provide the easiest solution until we have more information about the tables.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1