union all returns duplicate rows, Union should not, (on MS SQL SERVER). Also you chould use QofQ s you get what ever combo you want...
Justin > -----Original Message----- > From: Adrian Lynch [mailto:[EMAIL PROTECTED]] > Sent: 10 September 2002 14:37 > To: '[EMAIL PROTECTED]' > Subject: RE: [ cf-dev ] SQL UNION > > > Would UNION ALL give the correct results? > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] > Sent: 10 September 2002 14:36 > To: [EMAIL PROTECTED] > Subject: Re: [ cf-dev ] SQL UNION > > > > hmm. First question, no Order By, it joined the two selects together and > ordered them alphabetically, regardless of which table they came from. > > Second question. Can someone explain this to me. Here's the queries and > record counts (just using some test data here): > > getNames (Records=2489, Time=94ms) > SQL = > SELECT Name > FROM Guestbook > > getIPs (Records=178, Time=16ms) > SQL = > SELECT Name > FROM Guestbook > WHERE IPAddress = "212.250.157.251" > > getStuff (Records=993, Time=78ms) > SQL = > SELECT Name > FROM Guestbook > > UNION > > SELECT Name > FROM Guestbook > WHERE IPAddress = "212.250.157.251" > > > er, some kind of discrepancy. Query 1 gives 2489 rows. Query 2 gives 178 > rows. Unioned together, I get 993 rows. What does that mean? I'm sure > there's a simple logical explanation that I can't see. I would have > expected to get probably 2489 rows back, or possibly 2667, but I > can't work > out where 993 comes from at all. > > > > Duncan Cumming > IT Manager > > http://www.alienationdesign.co.uk > mailto:[EMAIL PROTECTED] > Tel: 0141 575 9700 > Fax: 0141 575 9600 > > Creative solutions in a technical world > > ---------------------------------------------------------------------- > Get your domain names online from: > http://www.alienationdomains.co.uk > Reseller options available! > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > > > > > duncan.cumming@alienationde > > sign.co.uk To: > [EMAIL PROTECTED] > cc: > > 09/10/02 02:07 PM Subject: > [ cf-dev > ] SQL UNION > Please respond to dev > > > > > > > > > If I have > > SELECT a, b > FROM tableA > WHERE Dept = 1 > > UNION > > SELECT a, b > FROM tableB > > without any OrderBy clause, what order will the results be in? > tableA rows > followed by tableB rows, or could they be in any order at all? > > > Second question. If I have UNION on two selects from the same table, eg: > > SELECT a > FROM tableA > WHERE Dept = 1 > > UNION > > SELECT a > FROM tableA > WHERE foo = 'bar' > > The two selects don't return mutually exclusive rows, i.e. there is an > overlap. Assume my table looks like > > ID Dept foo > 1 1 bar > 2 1 notbar > 3 2 bar > 4 3 notbar > > first select would return rows with ID 1 and 2 > second select would return rows with ID 1 and 3 > Overall would the Union give me 1, 2, 3, or would it give me 1, 2, 1, 3? > > > Duncan Cumming > IT Manager > > http://www.alienationdesign.co.uk > mailto:[EMAIL PROTECTED] > Tel: 0141 575 9700 > Fax: 0141 575 9600 > > Creative solutions in a technical world > > ---------------------------------------------------------------------- > Get your domain names online from: > http://www.alienationdomains.co.uk > Reseller options available! > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
