Sql table merge help SOLVED

Databases
1

Very simply I have 2 tables.

TB1 knows the marriage between <.name> and <id#>
TB2 knows the marriage between <id#> and <desk#>

I need either table to absorb the other. There are many many many other variables on these tables but specifically I am needing the ability to export a CSV where all 3 of the above values are married together.

I did another query and ended up having all 3 on the same table, but the marriages were lost so I had for example <.name1><.id1><.desk300>

I’m sure this is a simple concept but everywhere I’m looking its just talking about importing columns with no regard to the previous data.

once they are married I can just do a “select * blah blah desk# between 300 and 400” and get a list of names etc.

Any help is welcome. Feel free to just send me to a w3 learning document if it exists. But I looked haha! :slight_smile:

2

Better yet actually a SQL query that pulls data from both would work best. That way data doesn’t have to be updated every time it changes in one to change in the other.

3

Post a sql dump of your db schema and a few sample records.

4

nevermind figured it out. simple search just had bad brain.

For future searchers you just have to do something like this :

SELECT table1.name, table2.desk FROM table1, table2 WHERE table1.id= table2.id

5

You would think. Dont dont join on in a WHERE condition. Use a proper table JOIN

6

Can you explain further? the query has been working fine since I wrote it. Made sure it didn’t miss any data.

7 
SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);

SELECT `name`, desk 
FROM table1
INNER JOIN table2 USING (id)

FYI, do not use name as a column name. It is a reserved word (keyword). You need to use backticks otherwise.

8

no worries; name was just for this site; the actual column is first_name and last_name etc.

as for using the join - what are the benefits of doing it that way or what are the defecits from doing it the original way?

9

the syntax which uses USING is fairly limited, in that you can use it only if the tables are to be joined on a column which has the same name in both tables

the more general form, which you should adopt, is

SELECT
  FROM table1
[ INNER | LEFT | RIGHT | FULL [ CROSS ] JOIN table2
    [ON table2.somecolumn = table1.someothercolumn] *

* ON clause not applicable to CROSS joins

as for the original syntax, in which the tables are mentioned in a comma-delimited list in the FROM clause, with the join condition(s) in the WHERE clause, the deficits are

  1. it works only for inner and cross joins

  2. when there are multiple tables it’s ~so~ easy to mess up your join conditions and not catch it

  3. it’s been out of date since 1992!!!

2 Likes
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.