Hello;
I want merge four MySQL tables to get the intersection that have a common field for all of them. Join two tables is fine to me, but my this case is different from common situations and there are not very many discussions about it. Can anybody give me some idea? Thanks a lot!
Here is part of my simplified tables (out of ~30000 rows per table and more than 10 columns of complicate formats, so that I do not want use shell command JOIN or other want to do the job!):
The output is expected to be:
My Code is:
SELECT
B.ID, B.AGI,
C.ID, C.AGI,
D.ID, D.AGI,
A.ID, A.AGI FROM TableB B, TableC C, TableD D, TableA A WHERE
B.AGI = A.ID
AND C.AGI = A.ID
AND D.AGI = A.ID
AND B.AGI = C.AGI
AND D.AGI = C.AGI
AND B.AGI = D.AGI;
But the output is not what I expected:
Can anybody give me some idea? Got lost with the LEFT/ RIGHT/INNER JOINs. Expert suggestion can save me days of scratch. Thanks a lot!
What I wanted is to pick up all rows of the three tables (B, C & D) by the common key from col1 of TableA, i.e. A1 ~ A6, if B, C & D share any key of each col2 of B, C & D. The 14 rows output seems to be correct, but with exhaust combination of the four tables.
The fact for this study is to pick up all the common genes of the three organisms with Arabidopsis (model plant) as the reference(key).
At first I was thinking about the technique part to retrieve common rows of four tables (3 tables at least). Now I need to think it over after Tyler's code for my purpose, as I did not take the redundant items (key) in B, C & D although items of col1 in TableA are unique.
Thank you anyway.
What I wanted is to pick up all rows of the three tables (B, C & D) by the common key from col1 of TableA, i.e. A1 ~ A6, if B, C & D share any key of each col2 of B, C & D.
...
Not sure I understood that fully, but let's say you had only two tables "tablea" and "tableb". And they had data as follows -
What would have been your expected output in that case?
What I have:
I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory)
note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates... (3 Replies)
my queryis :
select distinct m.name, item_count, item from master m join client p on m.name=p.name where item_count = 1 and item > 1;
But how should I update them?
i used update statetment :
Update from client Set item =1 where m.name=p.name and item_count=1 AND item>1
Is this wrong? (1 Reply)
Hello all;
I have been really frustrated with finding the correct perl code (and MySql statements) to accomplish what I thought was straight forward...I have tested I don't know how many different codes\suggestions I found on the net without any success...but anyhow let me explain my plight and... (0 Replies)
Hello everybody,
I'm having troubles creating a relation between two tables in a MySQL database.
Having two tables, being one which contains users information (username, password, user ID, etc) and the other the one which contains transactions information (operation type, user ID of the user who... (2 Replies)
Hi all,
I have 2 mysql databases running on two different servers (both can be accessed via ssh to each other).
If I have say table ABC on server 1, how can I sync it with table ABC on server 2 if the number of records is different? Both databases are the same structure though server 2 has... (2 Replies)
How would one go about creating 'day' tables based on the timestamp field.
I have some 'import' tables which contains data from various days and would like to spilt that data up into 'days' based on the timestamp field in new tables.
TABLE_IMPORT1
TABLE_IMPORT2
TABLE_IMPORT3
... (2 Replies)