SQL Select in two tables MySQL

Hi all.

I have two tables in mysql db:


TBL_1		|	TBL_2
------------------------------------
ID	name	|	ID_1	name
1	jack	|	1	jack
2	jane	|	1	jack
3	paul	|	2	jane
4	franck	|	2	jane
		|	3	paul

I need extract all rows of TBL_1 and the rows of TBL_2 if present in TBL_1.

Can you help me?
kind regards
viki

SELECT DISTINCT TBL_1.ID, TBL_2.ID_1, TBL_1.name, TBL_2.name FROM TBL_1 INNER JOIN TBL_2 ON TBL_1.ID = TBL_2.ID_1 AND TBL_1.name=TBL_2.name

That should do it :slight_smile:

The query selects all fields from both tables, where the ID of TBL_1 is equal to ID_1 from TBL_2 and TBL_1.name and TBL_2.name are equal (this is what the INNER JOIN … ON … AND … is for)

Finally, to avoid getting back duplicate rows, i put DISTINCT after SELECT, which tells MySQL that you don’t want to get duplicate rows back (rows that have the same value in every field)

Hi and thanks for your answer.

My query SQL semplified is:


SELECT DISTINCT
tbl_1.*,
tbl_2.*
FROM
tbl_1
INNER JOIN
tbl_2
ON
tbl_1.ID = tbl_2.ID_1

The result is only rows of tbl_2… and all rows of tbl_1 ?

SELECT ...
  FROM table1
[B][COLOR="Blue"]LEFT OUTER
  JOIN[/COLOR][/B] table2
    ON table2.id1 = table1.id

:cool:

Hi r397, howe are you?

thanks x your reply, but I have problem whit your query.

The tables:

TBL_1		|	TBL_2
------------------------------------
ID	name	|	ID_1	name
54	jack	|	54	jack
           	|	54	jack

Whit query LEFT OUTER JOIN I see rows of TBL_2 ( 54 jack ), but NOT see the row of TBL_1 ( 54 jack ).

Why not see three rows ( 2 rows TBL_2 + 1 row TBL_1 ) ?

kind regards
viki

please show your exact query, your exact results, and then show what you expected

i have a feeling you mixed up the table names because they aren’t really called TBL_1 and TBL_2, are they

OK.

The two tables:

TBL_1		|	TBL_2
------------------------------------
ID	name	|	ID_1	name
54	jack	|	54	jack
           	|	54	jack

The query is:

SELECT * FROM
   tbl_1
LEFT OUTER
   JOIN tbl_2
      ON tbl_1.ID = tbl_2.ID_1

Exact results of the query is:

54 jack ===> TBL_2
54 jack ===> TBL_2

I need this rows :

54 jack ===> TBL_2
54 jack ===> TBL_2
54 jack ===> TBL_1

SELECT ID,name FROM TBL_1
UNION ALL
SELECT ID,name FROM TBL_2

many thanks x your help! :slight_smile: