CODEWITHSUNDEEP
mysqlsqldatabase
JAVAGeek
JAVAGeek

Reputation: 2794

get result from multiple tables

I have a table orders like this -

id  | bookId 
------------
1      3              
2      2               
3      1

and this books -

bookId  |  book
---------------
  1       bookA
  2       bookB
  3       bookC

is their any way i can get book column under bookId when i do

select * from orders where id = '1'

so that result would be like -

id  | bookId 
------------
1      bookC         <------- bookC instead of 3

Upvotes: 0

Views: 74

Answers (3)

Sean Redmond
Sean Redmond

Reputation: 4114

After doing a JOIN, to fetch the column under a different name you just need to say you want to get b.book AS bookId

SELECT o.id, b.book as bookId
FROM orders o
INNER JOIN books b
    ON o.bookId = b.id
WHERE o.id = 1

(Untested, the DBMS may complain about the similarity in column names)

Upvotes: 1

Air
Air

Reputation: 8595

There are two parts to what you're asking. The first is that you will need to perform a JOIN to get information from more than one table, according to some relationship between those tables (in this case, the relationship is defined by the shared bookId column).

Check out this answer to a question about joins for several references to help explain what a JOIN does. Other answers have already given you some code samples to work with, and for a basic tutorial there's always w3schools.

The second part is that you want to return information from books.book but you don't want to call the column something other than book. So you have to use an alias, which is done very easily with the AS keyword (and this is also often used with JOIN). However, it's probably not a good idea to alias one column (books.book) by an existing name of another column (orders.bookId). The existing bookId column contains integers, not strings. It will be less confusing in the long run if you choose a more specific alias.

In order to use a column alias, you have to specifically refer to the column you want to alias in your SELECT statement. For instance: SELECT book FROM books AS your_alias_here.

Here's an example of a simple way to do what I think you want:

SELECT orders.id, books.book AS your_alias_here
FROM  orders INNER JOIN books USING(bookId)

Since AS follows books.book, the alias is applied to the book field from the books table.

Upvotes: 0

Taryn
Taryn

Reputation: 247680

You will need to JOIN the tables on the bookid column in the orders table to the bookid column in the books table:

select o.id, b.book as bookId
from orders o
inner join books b
  on o.bookid = b.bookid
where o.id = 1;

Upvotes: 2

Related Questions