Select record from 3 tables using inner and outer join in mysql

Question

0.00/5 (No votes)

See more:

I want to select records

`product_id 	product_name 	actual_price 	discount_price` from `oc_product, oc_product_description, oc_product_special`. `oc_product_special`

table contains discount price of product but on some products there is no discount i.e. discount_price=0.0000. I want to select all 40 products with their discount_price and if discount_price=0.0000 then print NULL in that row.

Query which I tried is:

SQL

`SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price FROM oc_product INNER JOIN oc_product_description RIGHT OUTER JOIN oc_product_special ON oc_product_description.product_id = oc_product.product_id = oc_product_special.product_id`

it returns me wrong output like

`product_id 	product_name 	actual_price 	discount_price
  NULL 	          NULL 	           NULL 	       0.0000
  NULL            NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       0.0000
  NULL 	          NULL 	           NULL 	       1950.0000
  NULL 	          NULL 	           NULL 	       3850.0000
  NULL 	          NULL 	           NULL 	       7500.0000`

And I expect this output:

`product_id 	product_name 	actual_price 	discount_price
  1               yyyy 	           1000.0000 	  0.0000
  2               xxxx 	           2000.0000 	  500.0000`

Posted 9-Aug-15 19:19pm

Vilas Galave

Updated 9-Aug-15 20:57pm

Maciej Los

v3

Add a Solution

Comments

Advay Pandya 10-Aug-15 1:53am

Can you please put some clarity in your question ? What is wrong in your result and what are you trying to achieve with this query ?

Vilas Galave 10-Aug-15 2:21am

@Advay Pandya - see my edited question

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-09T21:22:00

Seems your query is wrong.

It should be something like:

SQL

SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price
FROM oc_product
    INNER JOIN oc_product_description ON oc_product_description.product_id = oc_product.product_id 
    RIGHT OUTER JOIN oc_product_special ON oc_product.product_id = oc_product_special.product_id

For further information, please see: 13.2.9.2 JOIN Syntax[^]

I'd suggest to use table aliases[^] to make query more clear.

Advay Pandya · Accepted Answer · 2015-08-10T00:06:00

Hello,

I agreed with the solution by [Maciej Los^].

In addition to the solution want to add one more thing.

As per my understanding of your query and requirement, "oc_product_special" table contains discount information.(If applicable). So, it should be Left outer join between oc_product and oc_product_special tables. Because in your query result of left side tables is more important. so, u will not get the product_Id with NULL values.

Please try below query:

SQL

SELECT oc_product_description.product_id,oc_product_description.name product_name, oc_product.price actual_price, oc_product_special.price discount_price
FROM oc_product
    INNER JOIN oc_product_description ON oc_product_description.product_id = oc_product.product_id
    LEFT OUTER JOIN oc_product_special ON oc_product.product_id = oc_product_special.product_id

Please let me know if you have any concern or query on this.

Thanks

Select record from 3 tables using inner and outer join in mysql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0