MySQL join four tables! Post: 302592441

Sponsored Content

Top Forums Programming MySQL join four tables! Post 302592441 by durden_tyler on Tuesday 24th of January 2012 12:11:42 AM

01-24-2012

Registered User

Code:

mysql>
mysql>
mysql> select * from tablea;
+----+-----+
| id | agi |
+----+-----+
| A1 | O   |
| A2 | P   |
| A3 | Q   |
| A4 | R   |
| A5 | S   |
| A6 | T   |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tableb;
+----+-----+
| id | agi |
+----+-----+
| B1 | A1  |
| B2 | A2  |
| B3 | A3  |
| B4 | A4  |
| B5 | A1  |
| B6 | A2  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tablec;
+----+-----+
| id | agi |
+----+-----+
| C1 | A1  |
| C2 | A2  |
| C3 | A2  |
| C4 | A4  |
| C5 | A4  |
| C6 | A1  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql> select * from tabled;
+----+-----+
| id | agi |
+----+-----+
| D1 | A1  |
| D2 | A2  |
| D3 | A3  |
| D4 | A4  |
| D5 | A5  |
| D6 | A1  |
+----+-----+
6 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- query
mysql> select b.id, b.agi,
    ->        c.id, c.agi,
    ->        d.id, d.agi,
    ->        a.agi
    ->   from tablea a,
    ->        (select agi, min(id) as id from tableb group by agi) b,
    ->        (select agi, min(id) as id from tablec group by agi) c,
    ->        (select agi, min(id) as id from tabled group by agi) d
    ->  where a.id = b.agi
    ->    and a.id = c.agi
    ->    and a.id = d.agi;
+------+-----+------+-----+------+-----+-----+
| id   | agi | id   | agi | id   | agi | agi |
+------+-----+------+-----+------+-----+-----+
| B1   | A1  | C1   | A1  | D1   | A1  | O   |
| B2   | A2  | C2   | A2  | D2   | A2  | P   |
| B4   | A4  | C4   | A4  | D4   | A4  | R   |
+------+-----+------+-----+------+-----+-----+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql>

tyler_durden

Last edited by durden_tyler; 01-24-2012 at 09:09 AM..

This User Gave Thanks to durden_tyler For This Post:

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

7 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

create 'day' tables based on timestamp in mysql

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. Web Development

How do you sync tables in mysql between two different servers?

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...

3. Programming

MySQL: Create a relation between two tables.

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...

4. Programming

Query to join two tables in SQL

Hi, I have 2 tables. Table1 name map_code data_code id aaa 2732C 3333B 10 bbb 1223F 2545v 11 ccc 12 Table2 name map_code data_code id text_description aaa 2732C 3333B 10 description 1 bbb 1223F 2545v 11 ...

5. Shell Programming and Scripting

Show only new and removed records by comparing to MySQL tables

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...

6. Shell Programming and Scripting

UPDATE COmmand post comparing 2 columns in 2 mysql tables

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?

7. Shell Programming and Scripting

Shell script automation using cron which query's MySQL Tables

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...

LEARN ABOUT CENTOS

pmdamysql

PMDAMYSQL(1)						       Performance Co-Pilot						      PMDAMYSQL(1)

NAME

       pmdamysql - MySQL database PMDA

DESCRIPTION

       pmdamysql is a Performance Co-Pilot PMDA which extracts live performance data from a running MySQL database.

INSTALLATION

       pmdamysql uses a configuration file from (in this order):

       o   /etc/pcpdbi.conf

       o   $PCP_PMDAS_DIR/mysql/mysql.conf

       This file can contain overridden values (Perl code) for the settings listed at the start of pmdamysql.pl, namely:

       o   database name (see DBI(3) for details)

       o   database user name

       o   database pass word

       Once this is setup, you can access the names and values for the mysql performance metrics by doing the following as root:

	       # cd $PCP_PMDAS_DIR/mysql
	       # ./Install

       If you want to undo the installation, do the following as root:

	       # cd $PCP_PMDAS_DIR/mysql
	       # ./Remove

       pmdamysql is launched by pmcd(1) and should never be executed directly.	The Install and Remove scripts notify pmcd(1) when the agent is
       installed or removed.

Binary Status values in text
       Some of the status values are in the form of YES/NO or ON/OFF.

       Since these cannot be intepreted by tools like PMIE, they have been duplicated with a _num extension and the values of 1 (YES/ON) or 0
       (NO/OFF).

   Eg:
       o   mysql.slave_status.slave_io_running

       o   mysql.slave_status.slave_io_running_num

FILES

       /etc/pcpdbi.conf
	   configuration file for all PCP database monitors

       $PCP_PMDAS_DIR/mysql/mysql.conf
	   configuration file for pmdamysql

       $PCP_PMDAS_DIR/mysql/Install
	   installation script for the pmdamysql agent

       $PCP_PMDAS_DIR/mysql/Remove
	   undo installation script for the pmdamysql agent

       $PCP_LOG_DIR/pmcd/mysql.log
	   default log file for error messages from pmdamysql

SEE ALSO

       pmcd(1), pmdadbping.pl(1) and DBI(3).  # vi: sw=4 ts=4 et:

3.8.10							       Performance Co-Pilot						      PMDAMYSQL(1)