Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: collecting and combining across tables

  1. #1
    Join Date
    Nov 2005
    Posts
    12

    collecting and combining across tables

    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

  2. #2
    Join Date
    Nov 2005
    Posts
    12
    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.

  3. #3
    Join Date
    Nov 2005
    Posts
    12
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  5. #5
    Join Date
    Nov 2005
    Posts
    12
    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    12
    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

  7. #7
    Join Date
    Nov 2005
    Posts
    12

    Unhappy

    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.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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?

  9. #9
    Join Date
    Nov 2005
    Posts
    12
    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
    Last edited by Jason Belec; 11-17-2005 at 12:04 PM.

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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.

  11. #11
    Join Date
    Nov 2005
    Posts
    12
    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?

  12. #12
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    It seems it runs for 2 minutes to produce nothing. Is there any setting in myssql to prevent displaying result set?

  13. #13
    Join Date
    Nov 2005
    Posts
    12
    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.

  14. #14
    Join Date
    Nov 2005
    Posts
    12
    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)

  15. #15
    Join Date
    Nov 2005
    Posts
    12
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •