select multiple rows from multiple table

Question

table1

 id  | name | can  | some
---- +------+------+-----
1    |A     | can1 | f
2    |B     | can0 | g
3    |C     | can1 | h
4    |d     | can2 | i

table2

id  | name | description
----+------+--------------
1   |can0  | some text
2   |can1  | another text
3   |can2  | text to

I have two tables. I want to get row with id=3 from table1and get description of can1 from table2.

I tried this

SELECT t1.* , t2.description 
from table1 as t1 , table2 as t2 
WHERE t1.can = t2.name

but this is not work.please help thanks

halfer · Accepted Answer · 2017-08-02 09:07:28Z

1

Maintain id of second table rather than text

table:- T1

id  | name | can    | can_id
---- +------+-------+--------
1    |A     | can1  |  2
2    |B     | can0  |  1
3    |C     | can1  |  2

Table:- T2

id  | name | description
----+------+--------------
1   |can0  | some text
2   |can1  | another text
3   |can2  | text to

Query Without maintain ID O table T2

select t1.*,t2.* from T1 as t1 join T2 as t2 on t1.can = t2.name

With Id

select  t1.*,t2.* from T1 as t1 join T2 as t2 on t1.can_id = t2.id

edited Aug 2, 2017 at 9:07

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Jul 31, 2017 at 10:22

swapnil kambe

1236 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ali karimi Over a year ago

thanks.i use second method "whit id" and work.but first method not work.

Barmar · Accepted Answer · 2017-07-31 07:33:35Z

0

You forgot to put t1.id = 3 in the WHERE clause.

SELECT t1.* , t2.description 
from table1 as t1 , table2 as t2 
WHERE t1.can = t2.name AND t1.id = 3

However, you should get more modern and use ANSI JOIN syntax rather than cross-products, as in Zeljka's answer.

answered Jul 31, 2017 at 7:33

Barmar

789k57 gold badges554 silver badges669 bronze badges

Comments

Dhruvang Gajjar · Accepted Answer · 2017-07-31 07:34:11Z

0

Try This...

SELECT t1.*, t2.* 
FROM table1 t1
JOIN table2 t2 ON t1.can = t2.name

answered Jul 31, 2017 at 7:34

Dhruvang Gajjar

5981 gold badge9 silver badges22 bronze badges

Comments

aidinMC · Accepted Answer · 2017-07-31 07:34:58Z

0

learn about join

SELECT t1.*,t2.description  FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id where t1.id = 3

answered Jul 31, 2017 at 7:34

aidinMC

1,4363 gold badges22 silver badges38 bronze badges

Comments

Zeljka · Accepted Answer · 2017-07-31 08:05:07Z

0

SELECT table1 .*, table2.description 
from table1
JOIN table2 on table1.can = table2.name
where table1.id =3

The best way is to join two tables, and use columns you need from joined table(in this case table2.description)

edited Jul 31, 2017 at 8:05

answered Jul 31, 2017 at 7:31

Zeljka

3761 silver badge10 bronze badges

3 Comments

Barmar Over a year ago

Don't just post code, explain what you changed and why.

Barmar Over a year ago

If you assign an alias to the table, you have to use the alias, you can't refer to table1.can and table1.id.

Zeljka Over a year ago

Oki, my mistake. I copied part of his code, i will edit my answer.. tnx

Bibhudatta Sahoo · Accepted Answer · 2017-07-31 10:06:06Z

0

You can do this with Inner Join like

SELECT table1 .*, table2.description 
from table1
JOIN table2 on table1.can = table2.name
where table1.id =3

Or use Left Join if Table2 is optional means if you don't have same table1.can and table2.name then also you will get all value from table1 then.
Do like this:

SELECT table1 .*, table2.description 
from table1
LEFT JOIN table2 on table1.can = table2.name
where table1.id =3

answered Jul 31, 2017 at 10:06

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

select multiple rows from multiple table

6 Answers 6

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related