collecting and combining across tables

[Jason Belec]

Hell, I'm still learning, but this is really giving me issues.

1) OK Table 1 has 49-50 fields of data, I want to exclude/remove 5 of these fields. Note the first field is the ID number to reference between files. About 250,000 lines of data so far.

Example: (6 of the fields)
1069 DAN 1 (nul) 10
1069 DAN 1 (nul) +001875 1140
1355 MAR 1 (nul) +009578 2980


2) This is Table 2 and I want to insert the 2nd and 3rd fields into the data in (1) matching the first field ID.

Example:
1069 Toronto, ON M2J 4V8
1167 Calgary, AB T3E 0G6
1206 Moncton, NB E1C 6X1
1355 Vancouver, BC V6G 2P1
1077 Sarnia, ON N7T 7H3
1315 Brackendale, BC V0N 1H0


3)The desired result is like this so that I can export to CVS.

Example:
1069 Toronto, ON M2J 4V8 DAN 1 (nul) 10
1069 Toronto, ON M2J 4V8 DAN 1 (nul) +001875 1140
1355 Vancouver, BC V6G 2P1 MAR 1 (nul) +009578 2980


I'd appreciate any direction and thank you.

Jason

[Jason Belec]

Oh, I apologize - mySQL 4.1. I have been using phpMyAdmin to learn and correct my mistakes and found that command line solves a lot of issues, but this still has me stumped.

[Jason Belec]

I've stumbled across some stuff and it is making earlier information a little more clear.

Perhaps someone could guide me a bit, however this seems to be what I want...

SELECT data2005.franchiseeID, data2005_Canadian.Area, data2005_Canadian.Zip_Code, data2005.upcid, data2005.quantity_sold
FROM data2005, data2005_Canadian
WHERE data2005.franchiseeID = 1069
AND data2005_Canadian.Canadian = 1069;

I have shortedened the select quite a bit...

jason

[skhanal]

You need to join ID columns from two tables in FROM clause

SELECT data2005.franchiseeID, data2005_Canadian.Area, data2005_Canadian.Zip_Code, data2005.upcid, data2005.quantity_sold
FROM data2005, data2005_Canadian
WHERE data2005.franchiseeID = data2005_Canadian.franchiseeID

[Jason Belec]

Thank you skhanal, my approach yielded the desired result for a single ID, not all the ID as I had hoped.

1069 | Toronto, ON | M2J 4V8 | 89540-45406 | | | | | 24.4968553459121 | | 24.4968553459121 | (edited for content)


Your approach got me the following...

mysql> SELECT data2005.franchiseeID, data2005_Canadian.Area, data2005_Canadian.Zip_Code, data2005.upcid, (edited for content)
-> FROM data2005, data2005_Canadian
-> WHERE data2005.franchiseeID = data2005_Canadian.Canadian;
Empty set (3 min 36.32 sec)

mysql>

Perhaps I took you far too literally. Please let me know what I missed.

Thank you.

[Jason Belec]

I'm guessing I need some kind of loop that grabs all possible matches to each ID in turn then gets the next ID and gets all possible matches, etc.

The way I have things currently look for a specific ID and seem to do as requested for all other parts, but the query doesn't advance to the next ID in the table.

Anyone have an idea what I'm doing wrong?

Thanks,

Jason

[Jason Belec]

I didn't think this was that hard. Apparently I've really bitten off a mouthful.

OK, perhaps someone could explain why the process works for an assigned ID for a match, but not a field or variable for the ID?

Then maybe I could problem solve.

[skhanal]

I thought franchiseeID was the column that linked two tables, but it seems data2005.franchiseeID is same as data2005_Canadian.Canadian

Are the data types same for two columns?

[Jason Belec]

Hello skhanal, thanks for checking back.

Here is the data in table 2:

"Canadian " "Area" "Zip Code"
1069 "Toronto, ON" "N7J 4V8"
1167 "Calgary, AB" "T7E 0G6"
1206 "Moncton, NB" "E1C 6X1"
1355 "Vancouver, BC" "V6G 2Q1"
1077 "Sarnia, ON" "N7T 7H3"
1315 "Brackendale, BC" "VPN 1H0"
1083 "Scarborough, ON" "M1P 1Z2"
1085 "Winnipeg, MB" "T3N 1E5"
1086 "Goulds, NL" "A1S 1E9"
1203 "N. Vancouver, BC" " X9M 2J5"
1344 "New Maryland,NB" " E3C 1H4"
1277 "Toronto, ON" "M2J 4V8"
1105 "Kelowna, BC" "V6W 1B8"
1035 "Calgary, AB" "T2V 0G6"
1109 "Newmarket, ON" " P6Y 6H8"
1034 "Mission, BC" "V2V 2L6"
1324 "Fort Saskatchewan, AB" "T9L 1Y8"
1026 "Metchosin, BC" "V9C 4B2"
1137 "Vancouver, BC" " I9M 2V6"
1074 "Nepean, ON" "K2J 2L2"
1058 "N. Vancouver, BC" "V7R 9U3"
1207 "Sylvan Lake, AB" "T4S 1J3"
1161 "Toronto, ON " " M7T 1P7"
1334 "Calgary, AB" "T8E 9W6"
1172 "Saskatoon, SK" "V6G 2P1"

Now the data in table 1 is over 6 million rows so I won't post it, but each ID will have anywhere from 1 to 2000 (approximate) rows pertaining to it. The goal was to add the table 2 data to each row referenceing the ID for output to a spreadsheet of csv file.

As for the ID fields in both tables, they are the same varchr(4), I thought of that myself and was fustrated that was not the problem.

I'm really stumped why the process won't repeat. Appreciate your efforts.

Jason

[skhanal]

Does mysql support ANSI syntax?. Try this

SELECT data2005.franchiseeID, data2005_Canadian.Area, data2005_Canadian.Zip_Code, data2005.upcid, data2005.quantity_sold
FROM data2005
inner join data2005_Canadian
on data2005.franchiseeID = data2005_Canadian.Canadian;

The earlier and this one is equivalent. Since the datatype and size are same there is no question about trimming blank spaces.

[Jason Belec]

Well I sure was hoping...

mysql> SELECT data2005.franchiseeID, data2005_Canadian.Area, data2005_Canadian.Zip_Code, data2005.upcid, data2005.quantity_sold
-> FROM data2005
-> inner join data2005_Canadian
-> on data2005.franchiseeID = data2005_Canadian.Canadian;
Empty set (2 min 52.02 sec)

mysql>

No matter what, if I select a hard value like 1069 for the ID, it works perfect. However no matter what I do for following the wonderful approach you have offered or some others I have modded from the web, I get this 'empty set' nonsense if I don't use a hard value.

Very, very fustrating to say the least.

Care to try again?

[skhanal]

It seems it runs for 2 minutes to produce nothing. Is there any setting in myssql to prevent displaying result set?

[Jason Belec]

Not that I am aware and it seems to work if I select a value so I'm stumped.

I even started completely from scratch thinking something went very wrong somewhere. Deleted everything, and reinstalled, rebuilt and reimported.

Still returns empty, very frustrating, I'm going to have to look at a php loop or something to get this done.

I do appreciate your help skhanal, you statements seem logical enough and I take it this works on your system.

I would like to get this sorted to at least know what the issue is.

[Jason Belec]

Tried this as well today that someone thought should solve things...

SELECT
table1.*,
table2.*
FROM table1
JOIN table2 ON table2.ID = table1.ID
ORDER BY <<you don't say what order you want>>;

Unfortunately, although it is faster, it still returns zippo...

Empty set (0.00 sec)

[Jason Belec]

Well skhanal, it looks like the fact that those ID fields were set as CHAR rather INT may have been the problem. I am at least getting some results now and will post my final solution after several hours of testing.