nested select statement/join statement...

Hello, I have 3 tables as shown below:


ProdName    ProdId    Desc
table               t1             Table
chair               c1             Chair
stool               s1             Stool
bed                 b1             bed


prodId   ven1                       ven2                    ven3                    ven4
t1                  100                        110                      120                       125
c1                  100                        115                      128                       123
s1                  100                        110                      120                       125
b1                  100                        119                    123                       128


VendorName      vendorId
vendor_1               ven1
vendor_2               ven2
vendor_3               ven3
vendor_4               ven4

I am trying to get the product pricing based on the vendor, for e.g.:

select p.prodName, p.ProdID, p.Desc, c.(select vendorId from from vendor where vendorName="vendor_1) from
product d, pricing c where p.prodId = c.prodId.

Looks I am doing something wrong here, can someone help me how to re-write this query.

Thank you,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
This code will work, but please read further explanation below this code:
SELECT  p.prodName,
        v1.VendorName AS Vendor1,
        c.ven1        AS VendPrice1,
        v2.VendorName AS Vendor2,
        c.ven2        AS VendPrice2,
        v3.VendorName AS Vendor3,
        c.ven3        AS VendPrice3,
        v4.VendorName AS Vendor4,
        c.ven4        AS VendPrice4
FROM    product p
INNER JOIN pricing c ON c.prodId = p.ProdId
INNER JOIN vendor v1 ON v1.VendorId = 'ven1'
INNER JOIN vendor v2 ON v1.VendorId = 'ven2'
INNER JOIN vendor v3 ON v1.VendorId = 'ven3'
INNER JOIN vendor v4 ON v1.VendorId = 'ven4'

Open in new window

The pricing table should not columns explicitly named as vendor ID's. You would have far more flexibility if it were designed simply as follows:
prodId   venorId    price
t1       ven1       100
t1       ven2       110
t1       ven3       120
t1       ven4       125
c1       ven1       100
c1       ven2       115
c1       ven3       128
c1       ven4       123
s1       ven1       100
s1       ven2       110
s1       ven3       120
s1       ven4       125
b1       ven1       100
b1       ven2       119
b1       ven3       123
b1       ven4       128

Open in new window

That way you are not restricting your vendors by having columns hard-coded to them. If you add more vendors in your vendor table, you can simply marry the vendor and product to this pricing table by the vendor and product IDs.

If you have it in your control to change this table, and you would like to see a revised query that works with this design, please let me know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.