Aniket Singh Aniket Singh - 5 months ago 11
SQL Question

How to fetch data from two different tables

I'm trying to fetch data from two different tables, the thing 3 tables have in common is username field, here is what I've tried till now

SELECT
*
FROM
post f1
INNER JOIN
portfolio f2 ON f1.username = f2.username
WHERE
f1.username IN ('user1' , 'user2')
OR f2.username IN ('user1' , 'user2');


All i want is if user1 has post data in
post
table & portfolio data in
portfolio
table then fetch all those detail

here is table structure

Post table

-------------------------------------------
| id | username | content | title | time |
-------------------------------------------
| 1 | user1 | eg | titl | eg1 |
| 2 | user2 | eg | title | eg2 |
| 3 | user3 | eg | title | eg3 |
| 4 | user1 | eg1 | title | eg4 |
| 5 | user5 | eg | title | eg5 |
| 6 | user6 | eg | title | eg6 |
-------------------------------------------


Portfolio table

-------------------------------------------
| id | username | Image | title | time |
-------------------------------------------
| 1 | user1 | eg | titl | eg1 |
| 2 | user2 | eg | title | eg2 |
| 3 | user4 | eg | title | eg3 |
| 4 | user1 | eg1 | title | eg4 |
| 5 | user3 | eg | title | eg5 |
| 6 | user6 | eg | title | eg6 |
-------------------------------------------


some of these user has data in post table also & portfolio table also so fetch the detail, how can i do it as my query is bringing duplicate data

Answer

You need only the firts condition the inner join worok only if match in the two table

  SELECT distinct f1.*, f2.* 
  FROM post f1
  INNER JOIN portfolio f2 ON f1.username = f2.username
  WHERE  f1.username IN ('user1' , 'user2'); 

or

  SELECT distinct f1.*, f2.* 
  FROM post f1
  INNER JOIN portfolio f2 ON 
          (f1.username = f2.username and  f1.username IN ('user1' , 'user2')); 

sqlfiddle http://sqlfiddle.com/#!9/1cfad/1

and second query http://sqlfiddle.com/#!9/1cfad/2

if you jave 3 table all with username in common you can use a double inner join

  SELECT distinct f1.*, f2.*, f3.* 
  FROM post f1
  INNER JOIN portfolio f2 ON f1.username = f2.username
  INNER JOIN table2 f3 on f1.username = f3.username
  WHERE  f1.username IN ('user1' , 'user2');