0

I've got two tables. One with part numbers, hardware names, and type and other with the locations of the hardware that also has locations of specific bins that contain the hardware. The bins don't have a specific number but have unique names. The second table also has the location of the hardware and bin which may change over time. And I'm trying to create a MySQL query that will combine the data in a new table that will be outputted as a comma separated file.

Table 1 Contents

Part Number | Name          | Type
------------+---------------+---------------
0           | None          | Not Applicable
25          | name1         | type1
150         | name2         | type2

Table 2 Contents

Date     | Bin  |  Part Number | Event    | To Location | From Location
---------+------+--------------+----------+-------------+---------------
1/1/2013 | bin1 |  0           | arrive   | location1   | none
1/2/2013 | none |  25          | arrive   | location2   | none
1/2/2013 | none |  150         | relocate | location3   | location2

The final output of the query should look something like:

Date     | Bin  | Part Number | Part Name | Type           | Event    | To Location | From Location
---------+------+-------------+-----------+----------------+----------+-------------+--------------
1/1/2013 | bin1 | 0           | None      | Not Applicable | arrive   | location1   | none
1/2/2013 | none | 25          | name1     | type1          | arrive   | location2   | none
1/2/2013 | none | 150         | name2     | type2          | relocate | location2   | location2
Barranka
  • 20,547
  • 13
  • 65
  • 83
tsapp1981
  • 41
  • 7
  • you cannot JOIN two tables together without having relating data. What in Table 1 relates to Table 2? Are there primary IDs? Are the IDs the relating info? please clarify. As far as a comma separated output, this can only be done after a query with a JOIN and then with PHP or phpMyAdmin output. Will you need this regularly or just once? – amaster Jul 17 '13 at 18:55
  • The relating data is the part number. We use the part number in Table 2 for movements because they are all unique while the part names are not necessarily unique. We are actually getting the output doing a fetchall and creating a .txt. – tsapp1981 Jul 17 '13 at 19:16
  • Sorry, I must have stared right at the Part Number in Table 2 and missed seeing it. How far have you gotten on the query? Did you even try to run any query? or were you expecting SE to do all the work for you? What problems are you having? – amaster Jul 17 '13 at 19:22
  • No worries, its a lot of information on just a couple of tables. I have successfully pulled all the data from table 2 and put it in a .txt file and have tried multiple queries from different threads on here but can't get the needed information into the output that I need. My latest which doesn't return anything is "SELECT table2.*, FROM table2 JOIN table1.name, table 1.type WHERE table 2.part number=table 1.part number;". I'm very new to this type of work and am learning as I go for a project, been stuck on the query for almost 2 days now so just trying to get some help or direction. – tsapp1981 Jul 17 '13 at 19:51
  • your syntax is off, see my answer below for the correct syntax – amaster Jul 17 '13 at 21:03

2 Answers2

0

may be this help full

select Name, Type, t2.Date, t2.Bin, t2.Part Number, t2.Event, t2.To Location, t2.From Location
FROM table1
JOIN table2 as t2    ON (table1.Part Number=t2.Part Number);

concept

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Nilesh patel
  • 1,216
  • 2
  • 14
  • 38
  • This returned a blank .txt so I'm not sure what is missing since I copied and pasted then changed all the names to my actual column names. – tsapp1981 Jul 17 '13 at 19:41
0

Try this:

SELECT
  *
FROM
  `Table1`
  INNER JOIN `Table2` ON (`Table1`.`Part Number`=`Table2`.`Part Number`)

To make the query better, you would want to define all the columns that you wanted returned instead of *

amaster
  • 1,915
  • 5
  • 25
  • 51
  • That worked perfectly once I added the column definitions in the order I wanted and I also added a ORDER BY to the query to get everything in date order. – tsapp1981 Jul 17 '13 at 21:34
  • Forgot to include the code. Thanks again for your help. The final solution was; `SELECT table2.date, table2.bin, table2.part number, table1.name, table1.type, table2.event, table2.final location, table2.from location FROM table1 INNER JOIN table2 ON table1.part number=table2.part number ORDER BY table2.date ASC;` – tsapp1981 Jul 17 '13 at 21:44
  • thanks please mark as best answer if it answered your question. – amaster Jul 17 '13 at 21:48