0

this is probably a crazy question but I am curious if anyone else has seen this. I have a database with 150 fields and 717002 records in it. If I do a count on business type like this:

SELECT COUNT(*) FROM contractors WHERE BusinessTypeString LIKE '%A5%';

I get 60898 records which is fine. But when I write out a statement to add in the headers and write it to an outfile I get 60604 which is 294 records less. No errors Below is the statement I used.

SELECT
  'BusinessName', 'DbaName', 'PhysicalAddressLine1', 'PhysicalAddressLine2', 
  'PhysicalAddressCity', 'PhysicalAddressProvinceOrState', 'PhysicalAddressZipPostalCode', 
  'PhysicalAddressZipCodePlus4', 'PhysicalAddressCountryCode', 'CorporateUrl', 
  '1stBusPocFirstName', '1stBusPocMiddleInitial', '1stBusPocLastName', '1stBusPocUsPhone', 
  '1stBusPocUsPhoneExt', '1stBusPocEmail'
UNION
(SELECT
  BusinessName, DbaName, PhysicalAddressLine1, PhysicalAddressLine2,
  PhysicalAddressCity, PhysicalAddressProvinceOrState, PhysicalAddressZipPostalCode, 
  PhysicalAddressZipCodePlus4, PhysicalAddressCountryCode, CorporateUrl, 
  1 stBusPocFirstName, 1 stBusPocMiddleInitial, 1 stBusPocLastName, 1 stBusPocUsPhone, 
  1 stBusPocUsPhoneExt, 1 stBusPocEmail 
  INTO OUTFILE '/home/VOB_Full.csv' FIELDS TERMINATED BY ',' 
  OPTIONALLY ENCLOSED BY '"'
FROM contractors
WHERE BusinessTypeString LIKE '%A5%');

Any ideas?

FanoFN
  • 6,815
  • 2
  • 13
  • 33
JAYCO Cloud
  • 1
  • 3
  • 1
    What if you use `UNION ALL`? how many records would you get? – FanoFN Oct 07 '20 at 00:45
  • @tcadidot0 I just ran It and I got 60899 which would include the header line which is correct. But what changes by using Union All. – JAYCO Cloud Oct 07 '20 at 01:50
  • There are differences between `UNION` and `UNION ALL` and I believe it has been covered many times before in SO but you can refer to this question https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – FanoFN Oct 07 '20 at 01:58
  • thank you I did search but I did not search on union, – JAYCO Cloud Oct 07 '20 at 02:38
  • I think the first step of every troubleshooting should start with _"what changes I did that made the result different?"_ Its clear that in your question, the query was returning your desired output before you did the `UNION`. Also I notice that the columns in your sub-query for all starting with `1 stBus ...` have space between `1` and `stBus..` . Is that intentional or was it a typo? The way it is right now, all those columns will return value `1`. – FanoFN Oct 07 '20 at 02:47

0 Answers0