SQL help

[musicman]

I've just started learning SQL and for some reason I can't wrap my brain around it. Yes this is homework, but if someone would point me in the right directions, I believe I complete the rest of it.

Given these tables:

Customer
cus_code
cus_lname
cus_fname
cus_initial
cus_areacode
cus_phone
cus_balance

Line
inv_number
line_number
p_code
line_units
line_price

Invoice
inv_number
cus_code
inv_date

Product
p_code
p_descript
p_indate
p_qoh (quantity on hand)
p_min (minimum)
p_price
p_discount
v_code

1. Write a query to display the customer code, invoice #, line #, product description, line units and line price for each item ordered Sort by customer code and invoice #

Here's what I have but I keep getting an error (ORA-01790: expression must have same datatype as corresponding expression
):

SELECT i.cus_code, i.inv_number, l.line_number
FROM invoice i join line l ON i.inv_number = l.inv_number
UNION
SELECT l.line_units, l.line_price, p.p_descript FROM line l join product p ON l.p_code = p.p_code;

I'm probably making it harder than it should be.

 

[mikeblas]

What you've coded is a UNION, which is far simpler than what you need to implement the requested query. To get this right, you'll need to code a multi-way inner join.

 

[musicman]

Thanks, that helps. Didn't realize what it took to get it to work could be done.

 

[Cardboard Hammer]

And the reason for the error message is that when you do a UNION, the sets must be column-by-column compatible. Whether or not the other columns are compatible, line_number and p_descript certainly seem not to be (presumtively of a numeric type and of a character type, respectively.)