joining 3 tables

[severman]

hi all
i was wonder if its possible to join 3 tables, so i could see the result from the 3 tables.
for example
lets say that i have
INVOICE(invoice_id, client_id,item_id)
CLIENTS(id, client_name)
ITEM(item_id, item_name)
i want 1 table that shows: invoice_id, client_name,item_name
is this possible?
10x!

[finito]

http://www.codingforums.com/showthread.php?t=398

[debasisdas]

How your tables are linked to each other ?

I don't think they are.

[finito]

How your tables are linked to each other ?

I don't think they are.

INVOICE(invoice_id, client_id,item_id)
CLIENTS(id, client_name)
ITEM(item_id, item_name)

I think he/she will have to client_id with id on client and Invoice then union on ITEM.item_ID = INVOICE.Item_ID

[severman]

if it wasn't clear
INVOICE(invoice_id, client_id,item_id)
CLIENTS(client_id, client_name)
ITEM(item_id, item_name)

client_id in invoice is FK IN CLIENTS
item_id in INVOICE is FK in ITEMS
so they are linked

oh, and union will not work because union returns one field, not multiple fields

[severman]

THANKS FINITO
you're link was very helpful!
this is the code of joining 4 tables for any one here who might want to know the answer:

select invoice_title.id, clients.name,items.item_des, invoice_line.amount
from invoice_title, clients, invoice_line,items
where invoice_title.client_id=client_id and invoice_line.item_id=items.item_id and
invoice_title.id=invoice_line.id  order by item_des;

[finito]

nps

[karant]

INVOICE(invoice_id, client_id,item_id)
CLIENTS(id, client_name)
ITEM(item_id, item_name)
i want 1 table that shows: invoice_id, client_name,item_name

select i.invoice_id ,c.client_name,it.item_name
from invoice i, client_name c,item_name it
where (i.client_id = c.client_id) AND (i.item_id = it.item_id)

[karant]

select i.invoice_id ,c.client_name,it.item_name
from invoice i, client_name c,item_name it
where (i.client_id = c.client_id) AND (i.item_id = it.item_id)

[mrahil2008]

hi all i was wonder if its possible to join 3 tables, so i could see the result from the 3 tables. for example lets say that i have INVOICE(invoice_id, client_id,item_id) CLIENTS(id, client_name) ITEM(item_id, item_name) i want 1 table that shows: invoice_id, client_name,item_name is this possible? 10x!

i assumed tables are joined according to id's.
u try this query and reply me if done

>>create table abc(invoice_id,client_name,item_nane)
as select i.invoice_id,c.client_name,it.item_name
from invoice i,client c,item it
where
i.item_id = it.item_id and
i.client_id = c.client_id;