yes indeed. Gave me 2667 rows, and the second select clause rows were at the end of the recordset.
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! ---------------------------------------------------------------------- ---------------------------------------------------------------------- Adrian Lynch <adrian.l@thoughtb To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> ubble.net> cc: Subject: RE: [ cf-dev ] SQL UNION 09/10/02 02:37 PM Please respond to dev 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]
