asked on

sql query

hello,
i am trying use a join to get all the data from both the tables, I am struggling to get dedires result

product
------------------
productid      productnumber stock
563            1334            13
564            1338            8

product_attributes
-------------------
poselid poselstock poselsku
p563:o26:12m      5            1335
p563:o26:18m      6            1336
p563:o26:6m        2            1334

I want to match the productnumber with poselsku and all the 3 records from the product_attributes table and show the stock from poselstock

The desired output would be
productid      productnumber      stock
-----------------------------------
563 1334 2
563 1335 5
563 1336 6
564 1338 8

I hope you see what I am trying to do?

Thanks

jimyX

You can use union:

Select * from product union select * from product_attributes

kswathi

Try the below code:

Select P.ProductId,
P.productnumber,
PA.Stock
from product P
left
outer join product_attributes PA
on P.productnumber = PA.poselsku

newbie27

ASKER

I am getting this exception ...

>>All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Also, if you could please note, for the attributes stock, I am not showing from the produts table, instead i want to show the stock available in the products_attributes table for the match

thanks

newbie27

ASKER

>>kswathi:

thanks

I am only getting one match from the attribtues table

563 1334 2
564 1338 8

kswathi

try right outer join

kswathi

try the links

or CROSS JOIN
http://en.wikipedia.org/wiki/Join_(SQL)

newbie27

ASKER

I am not getting it right, please advise

this is what I am trying to do

if we find the product with this match product.productnumber = product_attributes.productnumber

then fetch product.productid, and get all the rows from the product_attributes matching th poselid

product.productid = : 563
product_attributes = left(p563:o26:6m,4)

so basically for 563, there are 3 rows in the attributes table which I should get in the result ...

I hope someone may understand what I am trying to say?

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newbie27

ASKER

Thank you