CODEWITHSUNDEEP
phpmysqlsqljoin
user5312305
user5312305

Reputation:

Join in one column to another table

I know this question is very common on Stack, but I can't seem to find an answer or a snippet of code that actually solves my problem...

I have two tables, accounts and orders. I want to write a SQL statement to pull Order ID, Date, Total and Status from orders, and also Username from accounts.

The statement would be something like this:

$ohsql = "select * from orders where Username = '". $login_session ."'";

Obviously Username will come from the accounts table, but the principal is there. I am missing the join as I am clueless about it!

Upvotes: 0

Views: 75

Answers (2)

Rohit Gaikwad
Rohit Gaikwad

Reputation: 815

Assuming that your orders table contains the accountId and your accounts table containing the user name use following query

$ohsql = "select o.*, a.username from orders o
INNER JOIN accounts a ON a.id = o.accountId
WHERE a.username = '". $login_session ."'";

Let me know if you face any issue

Upvotes: 0

gyaani_guy
gyaani_guy

Reputation: 3209

You need to 'link' the two tables. For that do something like :
- add a column accountid to Orders table; so this tells us, which order belongs to which user . We then use this info in our JOIN.

Easy way to do it in 2 queries : 

// get the id value of the username
$id = select id from accounts table where username = $login_session 

// use that in the JOIN
select * from orders JOIN accounts ON orders.accountid = accounts.id where accounts.id = $id

Upvotes: 2

Related Questions