12 Replies - 29598 Views - Last Post: 16 April 2008 - 10:59 AM Rate Topic: -----

#1 Hanzie   User is offline

  • D.I.C Head

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

multiple INNER JOINS in one select-statement

Post icon  Posted 11 April 2008 - 03:02 PM

Hello, let's say I have 3 tables:

Table "Tekeningen" contains: "TekeningID", "Bladnummer", "Naam"
Table "Datums" contains: "DatumID", "Datum"
Table "Status" contains: "StatusID", "StatusNaam"

Now I wanna select with a stored procedure the value ""Bladnummer", "Naam" from table "Tekeningen" and the value "Datum" (must be the last date in the table "Datums" and the value "Status" from that date. (in dutch date is "Datum")

I have the folowing selectquery:

SELECT Tekeningen.Bladnummer, Tekeningen.Naam, MAX(convert(varchar, Datums.datum, 105)) AS Datum, Status.StatusNaam FROM Tekeningen INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID INNER JOIN Status ON Datums.DatumID = Status.DatumID WHERE Tekeningen.BedrijvenID = @BedrijvenID GROUP BY Tekeningen.Bladnummer, Tekeningen.Naam, Status.StatusNaam ORDER BY Bladnummer



But with this code I get the values i wanted, but i only wanna show it the value with the last date, not all dates.

Hope you guys know what i mean!?!

I believe it is in the double inner join queries.

Please help me further. Thnx

Is This A Good Question/Topic? 0
  • +

Replies To: multiple INNER JOINS in one select-statement

#2 souptoy   User is offline

  • D.I.C Head


Reputation: 54
  • View blog
  • Posts: 244
  • Joined: 17-January 08

Re: multiple INNER JOINS in one select-statement

Posted 11 April 2008 - 03:30 PM

I believe you're looking to set a search criteria in your SQL based off the last date entry? In that case you want to also have an AND in there underneath your WHERE statement with the datum = desired last date

This post has been edited by souptoy: 11 April 2008 - 03:31 PM

Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7380
  • View blog
  • Posts: 15,311
  • Joined: 16-October 07

Re: multiple INNER JOINS in one select-statement

Posted 11 April 2008 - 04:30 PM

View PostHanzie, on 11 Apr, 2008 - 06:02 PM, said:

Hope you guys know what i mean!?!


I think so.

First, for the max, you probably don't want to convert. (If you really do want to loose precision based on the convert mask, ignore that.) Whittle this down to a base query, to get the last date. I think this might do it:
SELECT Tekeningen.BedrijvenID, MAX(Datums.datum) AS Datum
	FROM Tekeningen 
		INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
	WHERE Tekeningen.BedrijvenID = @BedrijvenID 
	GROUP BY Tekeningen.BedrijvenID



Now, we put the rest back to together:
SELECT Tekeningen.Bladnummer, Tekeningen.Naam, convert(varchar, Datums.Datum, 105) AS Datum, Status.StatusNaam 
	FROM (
		SELECT Tekeningen.BedrijvenID, MAX(Datums.datum) AS Datum
			FROM Tekeningen 
				INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
			WHERE Tekeningen.BedrijvenID = @BedrijvenID 
			GROUP BY Tekeningen.BedrijvenID
		) a
	INNER JOIN Datums 
		ON Datums.Datum=a.Datum
	INNER JOIN Tekeningen 
		ON Tekeningen.BedrijvenID=a.BedrijvenID
			and Tekeningen.TekeningID = Datums.TekeningID
	INNER JOIN Status 
		ON Datums.DatumID = Status.DatumID 
	ORDER BY Tekeningen.Bladnummer



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 12 April 2008 - 10:31 AM

When I apply above suggested code it returns no rows at all?

I don't understand the character "a".

Can you explain what youre trying to do?

Thnx for the reply.
Was This Post Helpful? 0
  • +
  • -

#5 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 12 April 2008 - 11:01 AM

I have now the folowing selec-query. This results in the requested colums but i have a double row with two the same values only a different date, i only wan't the row containing the last date of these two rows.

the code i now have (so it on another site):

SELECT Tekeningen.Bladnummer, Tekeningen.Naam, max(convert(varchar, Datums.Datum, 105)) AS Datum, Status.StatusNaam 
	FROM Tekeningen inner join (Datums inner join status on Datums.DatumID = Status.DatumID) on Tekeningen.TekeningID=Datums.TekeningID where Tekeningen.BedrijvenID = @BedrijvenID group by tekeningen.bladnummer, Tekeningen.Naam, Status.statusnaam order by tekeningen.bladnummer 



please help!
Was This Post Helpful? 0
  • +
  • -

#6 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7380
  • View blog
  • Posts: 15,311
  • Joined: 16-October 07

Re: multiple INNER JOINS in one select-statement

Posted 12 April 2008 - 11:25 AM

View PostHanzie, on 12 Apr, 2008 - 01:31 PM, said:

When I apply above suggested code it returns no rows at all?


Neat. What if you just do the first one?


View PostHanzie, on 12 Apr, 2008 - 01:31 PM, said:

I don't understand the character "a".

Can you explain what youre trying to do?


Sure. The "a" is an alias. They're often used to make SQL statements more readable. For instance, your orginal SQL could be written like so:

SELECT t.Bladnummer, t.Naam, MAX(convert(varchar, dat.datum, 105)) AS Datum, s.StatusNaam 
	FROM Tekeningen t
		INNER JOIN Datums dat ON t.TekeningID = dat.TekeningID 
		INNER JOIN Status s ON dat.DatumID = s.DatumID 
	WHERE t.BedrijvenID = @BedrijvenID 
	GROUP BY t.Bladnummer, t.Naam, s.StatusNaam 
	ORDER BY t.Bladnummer



The alias value can be anything you like. It's usually used as a short hand. However, if you create a sub query, you need to name it, because it doesn't have a reference otherwise. e.g.
SELECT a.BedrijvenID, a.Datum
	FROM (
		SELECT Tekeningen.BedrijvenID, MAX(Datums.datum) AS Datum
			FROM Tekeningen
				INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID
			WHERE Tekeningen.BedrijvenID = @BedrijvenID
			GROUP BY Tekeningen.BedrijvenID
		) a



What I'm trying to do is this: Create a query, that sub query, that returns only the row or rows you're looking for, and build from there. The query you've offered will return a row for each unique combination of "t.Bladnummer, t.Naam, s.StatusNaam", which you didn't seem to want.

My assumption, given the use of the key, was that you wanted to values that related to the last Datums.datum for a given Tekeningen.BedrijvenID value. The select is something of a guess on my part, because I don't know your data. For instance, I'm assuming the Status table is included just to get a more informative value than StatusID?

If you get a result with what's presented, then you should reasonably get results from the first subquery. After that, I'm working backwards. My apologies, but given what I know, this is what I can offer.
Was This Post Helpful? 0
  • +
  • -

#7 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 14 April 2008 - 12:36 PM

Thnx again for the reply. I now understand this alias thing! Thnx.

I didn't understanded that i had to write both suggested codes. I thought only the first one. If I post both codes I don't get the rows i wan't.
This is because my introduction was wrong!!

The 3 tables contain:
Table "Tekeningen": "TekeningID", "Bladnummer", "Naam"
Table "Datums": "DatumID", "Datum"(the date), "TekeningID"
Table "Status": "StatusID", "Status", "DatumID"

Table "Tekeningen" is related trough the unmentioned table "Bedrijven", they are related trough the primary/foreign key "BedrijvenID"
Table "Tekeningen" and "Datums" are related trough "TekeningID"
Table "Datums" and "Status" are related trough "DatumID".

What I wan't is the rows from the colums "Bladnummer", "Naam", "Datum"(date), "Status".

But I only wan't the rows where the column "Datum"(Date) is the last date.
For example there are rows where the column "Bladnummer", "Naam", "Status" is all the same only the column "Datum" is different. Then I wan't it to only show the row with the last date("Datum").

When I run this code:

SELECT Tekeningen.Bladnummer, Tekeningen.naam, MAX(Datums.datum) AS Datum
	FROM Tekeningen 
		INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
	WHERE Tekeningen.BedrijvenID = @BedrijvenID 
	GROUP BY Tekeningen.Bladnummer, Tekeningen.naam



i get rows with folowing columns: "Bladnummer", "Naam" and the last date ("Datum").
Is it possible to after run this select-query add the belonging column "Status".

Hope you can help me further. I believe i'm close!!

This post has been edited by Hanzie: 14 April 2008 - 12:51 PM

Was This Post Helpful? 0
  • +
  • -

#8 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7380
  • View blog
  • Posts: 15,311
  • Joined: 16-October 07

Re: multiple INNER JOINS in one select-statement

Posted 14 April 2008 - 01:18 PM

Give this one a try:

SELECT Tekeningen.Bladnummer, Tekeningen.naam, LastDate.Datum, Status.StatusNaam
	FROM Tekeningen
		INNER JOIN (
			SELECT Tekeningen.BedrijvenID, MAX(Datums.Datum) AS Datum
				FROM Tekeningen 
					INNER JOIN Datums Datums 
						ON Tekeningen.TekeningID = Datums.TekeningID
				WHERE Tekeningen.BedrijvenID = @BedrijvenID 
				GROUP Tekeningen.BedrijvenID
		) LastDate 
			ON Tekeningen.BedrijvenID = LastDate.BedrijvenID 
		INNER JOIN Datums 
			ON Tekeningen.TekeningID = Datums.TekeningID 
				AND Datums.Datum = LastDate.Datum
		INNER JOIN Status 
			ON Datums.DatumID = Status.DatumID
	ORDER BY Tekeningen.Bladnummer



Here, we are doing much the same as before, but the "LastDate" result set is as narrowly defined as possible.

Good luck.
Was This Post Helpful? 0
  • +
  • -

#9 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 14 April 2008 - 01:23 PM

Thnx, but i get the error:

I get an error: incorrect syntax near "Tekeningen".

I'm getting confused here! This is more difficult then i thought!

HELP!
Was This Post Helpful? 0
  • +
  • -

#10 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 14 April 2008 - 02:04 PM

When i try this i get no rows returned, but empty columns:Bladnummer, Naam, Datum.

SELECT Tekeningen.Bladnummer, Tekeningen.naam, lastdate.datum
	FROM Tekeningen
		INNER JOIN (
			SELECT Tekeningen.BedrijvenID, MAX(Datums.Datum) AS Datum
				FROM Tekeningen 
					INNER JOIN Datums  
						ON Tekeningen.TekeningID = Datums.TekeningID
				WHERE Tekeningen.BedrijvenID = @BedrijvenID 
				GROUP by Tekeningen.BedrijvenID
		) LastDate 
			ON Tekeningen.BedrijvenID = LastDate.BedrijvenID 
		INNER JOIN Datums 
			ON Tekeningen.TekeningID = Datums.TekeningID 
				AND Datums.Datum = LastDate.Datum
		INNER JOIN Status 
			ON Datums.DatumID = Status.DatumID
			group by tekeningen.bladnummer, Tekeningen.naam, lastdate.datum
	ORDER BY Tekeningen.Bladnummer


Was This Post Helpful? 0
  • +
  • -

#11 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7380
  • View blog
  • Posts: 15,311
  • Joined: 16-October 07

Re: multiple INNER JOINS in one select-statement

Posted 14 April 2008 - 03:27 PM

I suspect your data is not as clean as I assumed. What is the final outcome supposed to look like, anyway? Nevermind.

Let's start with a "show me the everything" query:
select a.BedrijvenID, a.TekeningID, a.Bladnummer, a.Naam,
		b.DatumID, b.Datum,
		c.StatusID, c.Status
	from Tekeningen a
		left outer join Datums b
			on a.TekeningID=b.TekeningID
		left outer join Status c
			on c.DatumID=b.DatumID



Note the outer joins? This will show all records Tekeningen even if they don't have a reference in Datums or Status. Should they always have such an entry? This is a good starting point.

View PostHanzie, on 14 Apr, 2008 - 03:36 PM, said:

When I run this code:

SELECT Tekeningen.Bladnummer, Tekeningen.naam, MAX(Datums.datum) AS Datum
	FROM Tekeningen 
		INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
	WHERE Tekeningen.BedrijvenID = @BedrijvenID 
	GROUP BY Tekeningen.Bladnummer, Tekeningen.naam



i get rows with folowing columns: "Bladnummer", "Naam" and the last date ("Datum").
Is it possible to after run this select-query add the belonging column "Status".



Alright then, simple. Well, not really. The problem here is so much interplay bewteen data makes it hard to isolate elements. Particularly since I now know normal assumptions don't apply to your data. :P

First, I make a minor addition. This is should leave the data you like unchanged, but give me another datapoint to play with:
SELECT Tekeningen.Bladnummer, Tekeningen.naam, MAX(Datums.datum) AS Datum, Min(Tekeningen.TekeningID) as TekeningID
	FROM Tekeningen 
		INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
	WHERE Tekeningen.BedrijvenID = @BedrijvenID 
	GROUP BY Tekeningen.Bladnummer, Tekeningen.naam



Now, back to the subquery, but assume nothing.

SELECT a.Bladnummer, a.naam, a.Datum, Min(c.Status) as Status
	FROM (
		SELECT Tekeningen.Bladnummer, Tekeningen.naam, 
				MAX(Datums.datum) AS Datum, 
				Min(Tekeningen.TekeningID) as TekeningID
			FROM Tekeningen 
				INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
			WHERE Tekeningen.BedrijvenID = @BedrijvenID 
			GROUP BY Tekeningen.Bladnummer, Tekeningen.naam
		) a
		left outer join Datums b
			on a.TekeningID=b.TekeningID
		left outer join Status c
			on c.DatumID=b.DatumID
	GROUP BY a.Bladnummer, a.naam, a.Datum



There, that should do it. This is a pretty awful query and I'm not proud of it. However, when it comes to digging the data out of an existing data store, the customer is always right. ;)

I really, really hope this one works for you.
Was This Post Helpful? 0
  • +
  • -

#12 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 15 April 2008 - 11:30 AM

Hello,

Guess what: IT WORKS!!! :) :)

The code may not be so beatifull as you wanted it to be, but the most important thing for me is that it works!

'm very very thankfull for that!! Now I can go on with the program!

Thnx again!!! Maybe I can help somebody else over some time!
Was This Post Helpful? 0
  • +
  • -

#13 Hanzie   User is offline

  • D.I.C Head

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

Re: multiple INNER JOINS in one select-statement

Posted 16 April 2008 - 10:59 AM

Hello,

Just for the good order.
This code works too:

SELECT Tekeningen.Bladnummer, Tekeningen.Naam, convert(varchar, Datums.Datum, 105) AS Datum, Status.StatusNaam 
	FROM (
		SELECT Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam, MAX(Datums.datum) AS Datum
			FROM Tekeningen 
				INNER JOIN Datums ON Tekeningen.TekeningID = Datums.TekeningID 
			WHERE Tekeningen.BedrijvenID = @BedrijvenID 
			GROUP BY Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam 
		) a
	INNER JOIN Datums 
		ON Datums.Datum=a.Datum
	INNER JOIN Tekeningen 
		ON Tekeningen.BedrijvenID=a.BedrijvenID
			and Tekeningen.TekeningID = Datums.TekeningID
	INNER JOIN Status 
		ON Datums.DatumID = Status.DatumID 
	ORDER BY Tekeningen.Bladnummer, Tekeningen.Naam


Keep on coding!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1