Combining queries

[herbertioz]

I have 2 queries tha I'd like to combine and show all together, how can I do it without UNION? (Because you have to have tje same nr of columns)

Deltagere (Athletes), Vekt(Weight), Konkurranse (Competition), Lagnavn (Teamname), Klasse (Class)

My first query:

SELECT Deltagere.Lagnavn, Sum(Deltagere.Vekt) AS SumAvVekt
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn
ORDER BY Sum(Deltagere.Vekt) DESC

My second query:

SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn
ORDER BY Sum(Deltagere.Vekt) DESC

I hope someone can help with out here

 

[vbaInet]

This should do it:

SELECT Deltagere.Lagnavn, [COLOR=Red]Null[/COLOR], [COLOR=Red]Null[/COLOR], Sum(Deltagere.Vekt) AS SumAvVekt, [COLOR=Red]Null[/COLOR]
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn
[COLOR=Blue][B]UNION ALL[/B][/COLOR]
SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn
ORDER BY Sum(Deltagere.Vekt) DESC

Just match up the "missing" columns with Null.

 

[herbertioz]

That worked, but the results was not the right one. I want to display the TotalWeight for the teams with teamname, combined with the athetes individual weight all together.
I have the 2 queries but not a combined correct one

This should do it:

SELECT Deltagere.Lagnavn, [COLOR=Red]Null[/COLOR], [COLOR=Red]Null[/COLOR], Sum(Deltagere.Vekt) AS SumAvVekt, [COLOR=Red]Null[/COLOR]
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn
[COLOR=Blue][B]UNION ALL[/B][/COLOR]
SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn
ORDER BY Sum(Deltagere.Vekt) DESC

Just match up the "missing" columns with Null.

 

[vbaInet]

I wasn't looking at your logic. Did you get the right result in your original queries?

 

[herbertioz]

Yes, the first query show the sum (TotalWeight) of each team sorted.
The other query the athletes individual weigth sorted. I want to combine it together for a report. When I do, it fails. Suggestions? Select into select (subquery) or?

I wasn't looking at your logic. Did you get the right result in your original queries?

 

[vbaInet]

See if this gives you the desired result

Select * From 
(SELECT Deltagere.Lagnavn, Null, Null, Sum(Deltagere.Vekt) AS SumAvVekt, Null, 1 As SortField 
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn) AS Q
UNION ALL
Select * From 
(SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn, 2 As SortField 
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn) As P 
Order By SortField, SumAvVekt DESC;

However, if you're going to use it in a report and you want that sort order, then you must perform the sorting using the report's SORTING AND GROUPING functionality by first of all Grouping By SortField followed by a DESC sorting of SumAvVekt.

 

[herbertioz]

Not quite, because I dont know if the athletes here are on the team displayed in the first query. Hmm...
Thank you for the time

See if this gives you the desired result

Select * From 
(SELECT Deltagere.Lagnavn, Null, Null, Sum(Deltagere.Vekt) AS SumAvVekt, Null, 1 As SortField 
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn) AS Q
UNION ALL
Select * From 
(SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn, 2 As SortField 
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn) As P 
Order By SortField, SumAvVekt DESC;

However, if you're going to use it in a report and you want that sort order, then you must perform the sorting using the report's SORTING AND GROUPING functionality by first of all Grouping By SortField followed by a DESC sorting of SumAvVekt.

 

[vbaInet]

The alias field SortField tells you whether it's in the first or second query. Have a look and you will see that it generates the numbers 1 and 2.

 

[herbertioz]

Thats ok, I understand that.
I think I am not so good to explain what I need to do, but I try one more time from the start.

I have one table Deltagere (Athletes) and one table Konkurranse (Competitions).
One athlete can be a member of a team or not. It is a individual competition and a team competition. I wanna get out the results of the fishing competition, by Sum the total weight of the teams and sort it and also show the team members (athletes) with their individual weight in the same competition.

Do you understand?

The alias field SortField tells you whether it's in the first or second query. Have a look and you will see that it generates the numbers 1 and 2.

 

[vbaInet]

Hmm... not completely clear. Could you explain using some data like this:

Table 1

Athlete      |          Weight
---------------------
John M         |          70
John M         |          65
Harry K        |          90

Outcome of query 1:

Athlete       |         SumOfWeight
---------------------
[COLOR=Red][B]John M         |             135[/B][/COLOR]
Harry K         |        90

Then show the second table plus the overall output you're trying to achieve.

 

[herbertioz]

Teamname | Competition | SumWeightTeam
Team 1 Lake 1 80
Team 3 Lake 1 60
Team 4 Lake 1 45

Teamname | Competition | Athletes.Name | SumAthletesWeight
Team 1 Lake 1 John 20
Team 1 Lake 1 Peter 20
Team 1 Lake 1 Chris 40

All this in one query. It is better?

Hmm... not completely clear. Could you explain using some data like this:

Table 1

Athlete      |          Weight
---------------------
John M         |          70
John M         |          65
Harry K        |          90

Outcome of query 1:

Athlete       |         SumOfWeight
---------------------
[COLOR=Red][B]John M         |             135[/B][/COLOR]
Harry K         |        90

Then show the second table plus the overall output you're trying to achieve.

 

[vbaInet]

Ok, it looks like you would need to put both sql statements into seperate queries, so:

SELECT Deltagere.Lagnavn, Null, Null, Sum(Deltagere.Vekt) AS SumAvVekt, Null
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn

We can call this qryFirst.

Then,

SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn

Call this qrySecond.

Perform the UNION ALL based on those two queries, so:

Select * From qryFirst
Union All
Select * From qrySecond
Order By ...

 

[herbertioz]

The query show only the team nr 3 and not the other team members. I want to show all the athletes in the competition who also are on a team. Hard one...

Ok, it looks like you would need to put both sql statements into seperate queries, so:

SELECT Deltagere.Lagnavn, Null, Null, Sum(Deltagere.Vekt) AS SumAvVekt, Null
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
GROUP BY Deltagere.Lagnavn

We can call this qryFirst.

Then,

SELECT Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Sum(Deltagere.Vekt) AS SumAvVekt, Konkurranse.Konkurransenavn
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID = Deltagere.KonkurranseID
WHERE Konkurranse.konkurransenavn="Stokkevannet" AND Deltagere.Klasse="Herrer"
GROUP BY Deltagere.Navn, Deltagere.Klubb, Deltagere.Klasse, Konkurranse.Konkurransenavn

Call this qrySecond.

Perform the UNION ALL based on those two queries, so:

Select * From qryFirst
Union All
Select * From qrySecond
Order By ...

 

[vbaInet]

Can we see a version of your database with some sample data?