SQL UNION Query

[megetron]

Hi all,
I have a Union query and I want two things to change in it:
1. order by the count() ([points] third column) column.
2. group by the group_team.id the whole UNIIONs queries.

this is the query:

SELECT group_team.id,team.forum_id,3*count(match.home_team_id) as points,sum(match.home_team_goals) as Goals_plus,sum(match.guest_team_goals) as Goals_minus from group_team,match_gvion, match,team where
(match.home_team_goals>match.guest_team_goals) and
group_team.team_id=team.forum_id and
(match.home_team_id=team.forum_id) and
match_gvion.gvion_id=1 and match_gvion.match_id=match.id and
match.level<=3 and
match.status=1
group by group_team.id,team.forum_id
UNION

SELECT group_team.id, team.forum_id,3*count(match.guest_team_id),sum(match.guest_team_goals),sum(match.home_team_goals) from group_team,match_gvion,match,team where (match.home_team_goals<match.guest_team_goals) and
group_team.team_id=team.forum_id and
(match.guest_team_id=team.forum_id) and
match.level<=3 and
match_gvion.gvion_id=1 and match_gvion.match_id=match.id and
match.status=1
group by group_team.id,team.forum_id

UNION

SELECT group_team.id,team.forum_id,count(match.home_team_id),sum(match.home_team_goals),sum(match.guest_team_goals) from group_team,match_gvion,match,team where
group_team.team_id=team.forum_id and
(match.home_team_goals=match.guest_team_goals) and
(match.home_team_id=team.forum_id) and
match.level<=3 and
match_gvion.gvion_id=1 and match_gvion.match_id=match.id and
match.status=1
group by group_team.id, team.forum_id

UNION

SELECT group_team.id,team.forum_id,count(match.guest_team_id),sum(match.guest_team_goals),sum(match.home_team_goals) from group_team,match_gvion,match,team where
group_team.team_id=team.forum_id and
match.home_team_goals=match.guest_team_goals and
match_gvion.gvion_id=1 and match_gvion.match_id=match.id and
match.guest_team_id=team.forum_id and
match.level<=3 and
match.status=1
group by group_team.id,team.forum_id



ANY IDEAS? :wave:

[JeffB]

You can put this queries inside another one, for example you can do:

Select ... From

(
[Insert your UNION Queries here]
)

GROUP BY ...

You then can consider your union query to be ONE query. If you use Access, you could create a query with all the union, then a new query that use it to select. The result would be the same, only you can put all the sql in one query if you want.

JeffB

[jp140768]

I've just finished reading the "Teach Yourself SQL" book, and it implied for a union query that you could only put group and order by on the very last Select, as the Union creates one recordset, you can only order it in one way.

HTH

[megetron]

You can put this queries inside another one, for example you can do:

Select ... From

(
[Insert your UNION Queries here]
)

GROUP BY ...

You then can consider your union query to be ONE query. If you use Access, you could create a query with all the union, then a new query that use it to select. The result would be the same, only you can put all the sql in one query if you want.

JeffB
this is exactly what I've done, and it works like a magic. 10x alot

[Ezekielnin]

Hi!

I'm new here and I hope I can find someone who can help me with this query. All I need is to MAKE the UNION statement with this query:

SELECT DISTINCTROW Members.* FROM Members LEFT JOIN MemberChanges ON Members.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) )
ORDER BY Members.Div1 , Members.Div2 , Members.Div3 , Members.Div4

I would like to have the UNION between the PreMembers table and the Members table. Right now, as you can see, the query only concerns the Members table but I would like to have the results with also the PreMembers table. I'm pretty sure it's possible but I can't find a way to make it work.

Thanks a lot, Alex

[jp140768]

SELECT DISTINCTROW Members.* FROM Members LEFT JOIN MemberChanges ON Members.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) )
Union
Select .............
ORDER BY Members.Div1 , Members.Div2 , Members.Div3 , Members.Div4

The above should work for you, just fill in the second select statement, providing the field names are the same and in the same order:

The book I have says (SAMS Teach yourself SQL):

There are a few rules governing exactly what can be combined:
1). Each query in a union must contain the same columns, expressions, or aggregate functions
2). The columns, expressions and aggregates must occur in the exact same order in each Select statement in a union.
3). Column datatypes must be compatabile: The need not be the exact same type, but the must be of a type that the DBMS can implicitly convert (eg different numeric types or different date types).


HTH

[Ezekielnin]

I tried that and it gave me an error... it's seems that my member and premember table doesn't have the same structure .. so I guess it's impossible to link them with the Union statement.

anyways thanks

[jp140768]

Can you show the SQL statement you tried to run, along with the data types of the fields that you have selected? It may be possible to still get it to work.

[Ezekielnin]

Hi,

Here's the sql query I tried :

SELECT DISTINCTROW Members.* FROM Members LEFT JOIN MemberChanges ON Members.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) AND (Members.MemberType <> "DonationOnly") AND (Members.status = 'I') )
ORDER BY Members.Div1 , Members.Div2 , Members.Div3 , Members.Div4

UNION

SELECT DISTINCTROW PreMembers.* FROM PreMembers LEFT JOIN MemberChanges ON PreMembers.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) AND (PreMembers.MemberType <> "DonationOnly") AND (PreMembers.status = 'I') )
ORDER BY PreMembers.Div1 , PreMembers.Div2 , PreMembers.Div3 , PreMembers.Div4

When I try to run this, I receive an error «Access status 3190 - Too many fields defined SQL»

Just to precise a little bit: I'm using a program called ExCellerate. It's a church member database program. You will be able to find a fresh database here : http://www.msdweb.com/files/g12.mdb

I hope that this will help you out. If you have other questions on the program or database don't hesitate. You can also find a demo version on www.excellerate.com

Thanks a lot!

[jp140768]

How many records are in each table? The error you're getting implies that the problem is your select statement.

Will you try replacing the "SELECT DISTINCTROW Members.* " piece with say two or three columns from that table, same for the PreMembers table. Make sure you select the same columns in both tables.

Also remove the first ORDER BY statement.

Let us know how that works.

Why are you using a left join? Would you not be better to use an INNER JOIN? Based on your Where statement, there has to be a record on both tables for a record to be selected.

Talk to you later,

[Ezekielnin]

Alright, I'll try that right away.

But what's the dirrefence between Inner Join and Left join? The program I'm using is generating this code automatically... but I can always change it if I want to.

[Ezekielnin]

I tried this query :

SELECT DISTINCTROW Members.Lastname FROM Members INNER JOIN MemberChanges ON Members.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) AND (Members.MemberType <> "DonationOnly") AND (Members.status = 'I') )

UNION

SELECT DISTINCTROW PreMembers.Lastname FROM PreMembers INNER JOIN MemberChanges ON PreMembers.MemberID=MemberChanges.MemberID
WHERE ( (MemberChanges.ToValue = "I") AND (MemberChanges.Date >= :P0) AND (MemberChanges.Date <= :P1) AND (PreMembers.MemberType <> "DonationOnly") AND (PreMembers.status = 'I') )
ORDER BY Members.Lastname;

and it stills gives me an error ... you can check the image attached. What do you think it is?

[jp140768]

No idea, I reckon the problem is in one of your WHERE statements.

I know this is probably becomming painful, but can you run each SQL statement seperately. Do both produce an error statement?

On the one(s) that produce an error statement, will you run it without the conditions, then start puting them back one by one. Of course if you get an error with no conditions, its a totally different problem.

What value is contained in :P0 and :P1? Are these two field types (Members.MemberID=MemberChanges.MemberID) the same type and size?

We'll get to the end of it eventually.

[Ezekielnin]

Okay, I'll try to run the different SQL query separetly. Painfull it is .. but what the heck! I have to find out. ;)

to answer your question about the «:P0 and :P1», it's kinda part of the program. when the program reads :P0 it prompts the user for the value of the variable ... P0 is like «variable 0» if you want, P1 is «variable 1».

Thanks for your support.

Alexy

[Ezekielnin]

Okay,

I tried the Member query and it just work fine but the Premember table doesn't seem to have a MemberChange History variable. But I'm not sure about it. Perhaps I just mispelled the variable. Is there anyway to see what's inside a .mdb file without Microsoft access?

Thanks a lot!

Alexy

[Ezekielnin]

Check that out!

SELECT Members.*
FROM
Members
WHERE ( Year(Members.Anniversary = '2004') )

UNION

SELECT PreMembers.*
FROM
PreMembers
WHERE ( Year(PreMembers.Anniversary = '2004') )

ORDER BY PreMembers.Lastname;

This is also giving me an error ... Error 3190 - Too many fields defined SQL

Do you have an idea about this one?

[jp140768]

For a union query to work, the following must apply:

1). Each query in a union must contain the same columns, expressions, or aggregate functions
2). The columns, expressions and aggregates must occur in the exact same order in each Select statement in a union.

You said in one of your previous posts, that there is a different number of fields in the recordsets, so the only way to get the query to work is to list the fields in the select statement and ensure that each field in is the same position in your list eg:

Select A, B, C from tablea
union
select A, B, C from table b.

To see the field names in a database without Access, you can apply the following code:

dim adoFld as adodb.field

dim adoRS as adodb.recordset

'
' Set up your recordset and open it
'
for each adofld in adors
debug.print adoFld.Name
next


I haven't tried this, but I'm fairly sure it works. I posted this on the VB website, in a thread under Excel. If the above doesn't work, look for posts by me with Excel in the title and you'll get the answer, if not, I can look it out for you tomorrow.

HTH