CODEWITHSUNDEEP
sqldatabase
Datta
Datta

Reputation: 849

Getting data from one table to another table using join

I have a table name "a" 

 Id   name
 1    abc
 2    xyz
 3    mmm
 4    xxx

and Other table name is "b"

  Id    suId
   3     2
   3     1

My requirement is get detail from "a" table from "b" table where id=3. Any help?

Upvotes: 2

Views: 83

Answers (5)

Gaurav Gandhi
Gaurav Gandhi

Reputation: 3201

I wont recommend join for this kind of scenarios(you want all details from Table A whose ids are in Table B suId column, where Table B id should be 3., Its bad English but hope you got me and may be i got you too.)

SELECT a.name FROM a
WHERE 
a.id IN(SELECT b.suId FROM b WHERE b.id = 3);

If you want to use join only then,

SELECT a.name FROM a,b
WHERE a.id = b.suId
AND
b.id = 3;

Upvotes: 1

chris_techno25
chris_techno25

Reputation: 2477

You can try this...You can try different JOIN clauses like INNER JOIN, LEFT OUTER JOIN or just simply JOIN etc. You will get different number of rows depending on field connections from 1 table to the other.

SELECT T1.*
FROM a T1
INNER JOIN b T2
ON T1.Id = T2.Id
WHERE T1.Id='3'

Upvotes: 0

Nick Charney Kaye
Nick Charney Kaye

Reputation: 4340

This should get the job done:

SELECT * FROM table_a a JOIN table_b b ON b.suId = a.Id WHERE b.Id = 3;

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

Simple answer:

SELECT a.Id,a.name FROM a,b
WHERE a.Id=b.suId AND b.Id=3

It will give you the result:

Id    Name
1     abc
2     xyz

See result in SQL Fiddle

Upvotes: 0

nathancahill
nathancahill

Reputation: 10850

SELECT a.Id, a.name, b.Id, b.suId FROM b JOIN a ON b.suId = a.Id WHERE b.Id = 3;

Upvotes: 1

Related Questions