Link to home
Start Free TrialLog in
Avatar of qvfps
qvfps

asked on

Issue creating MySQL Query to insert distinct values into table.

I am trying to crate a table with results from multiple queries.  I have multiple tables with overlapping names which I want to consolidate into one table by customer with Distinct values with just the ID field of the original tables.  


I have added all names From Table1 for Cust2 to FinalTable but I am having an issue creating a query to add unique names from Talble2 for Customer2 to FinalTable.   


Table1

ID, T1Name, T1Date,     T1Cust, T1Field

1,  Fred,   2022-01-01, Cust1,  MiscData

2,  Bill,   2022-03-01, Cust1,  MiscData

3,  Jake,   2022-12-25, Cust2,  MiscData



Table2

ID  T2Name, T2Date,     T2Customer

1,  Fred,   2022-01-01, Customer1 

2,  Sam,    2023-03-01, Customer2 

3,  Bill,   2022-06-01, Customer1

4,  Jill,   2023-01-01, Customer2   



FinalTable (Cust2 = Customer2)

Name, T1ID, T2ID

Jake,  3,    

Sam,    ,    2

Sam,    ,    4


I have additional tables I need to consolidate once I get this working.

I am using MariaDB on OpenSUSE.

ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qvfps
qvfps

ASKER

Thanks for the query,

Sorry but I was not quite clear on what I am trying to do.  I have several tables and I want to create a single table showing what tables each name is included in. I want to limit to a single customer at a time and since the data is collected from different systems Customer is not consistent between all of them.  

You need a "primary key".  Something that ties the tables together and is unique to the customer.  It can be anything.  And if you find that there is a unique customer ID in Table X that is different that the Customer ID in other tables, you'll need a way to link them together.


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qvfps

ASKER

The final tables should be  
For Cust2=Customer2
Jake   3 , -
Sam    - ,2
Jill      -  ,4

For Cust1=Customer1
Fred    1,1
Bill       2,3

I have a table with the customer names for each source to use a cross reference.  The issue I am having is ending up with a single table with only one entry per name even if they are in multiple tables.  

the queries I am using are
1) CREATE TEMPORARY TABLE  'FinalTable' ('Name' TEXT NOT NULL,`ID1' INT NULL DEFAULT NULL , ID2  INT NULL DEFAULT NULL)
2) query1: INSERT into FinalTable (Name, ID1) select T1Name as Name, ID as T1ID FROM Table1 WHERE T1Cust = 'T1 Customer Name'
3) query2: INSERT into FinalTable (Name) select T2Name as Name FROM table2 WHERE T2Cust = 'T2 Customer Name' and Table2.name not in (SELECT Name FROM FinalTable)
4) Query3: UPDATE FinalTable T2ID set FinalTable.ID2=Table2.ID where FinalTable.Name=Table2.T2Name

When I run these queries I end up with Multiple lines per name instead of them all consolidated in one row.  
Avatar of qvfps

ASKER

I found the issue with the above queries.  One of the tables had duplicate entries in it for some names.   I am still interested if there is a better way to do this in fewer steps.
That is the first we've heard about a cross reference table which is why I suggested it.

To keep use from continue to guess at things:
Can you provide sample data for all three tables, table1, table2 and the cross reference table, and the expected results from the sample data you provide?

Please also provide your MySQL version.

Then we can set up a test case and provide tested SQL.