I want to know if there is any way that within a WHERE
if the result is 1
that it does such and if 0
it does such and such. as a IF
.
The problem I have is that I am wanting to attach the prices to a document. But one of the codes is priceless in price_code = 1
. Therefore, I delete the result line because it does not meet the condition.
Rescued from comments: If when I look price_code = 1
up it doesn't find a value then I should go look up the Cost in table costs
, column cost_price
.
I leave a summary:
SQL scripts:
SQL
SELECT a.item_code, a.trx_type, a.trx_num, a.qty, b.list_price
FROM moves AS a
INNER JOIN items_price AS b
ON a.item_code = b.item_code
INNER JOIN costs AS c
ON a.item_code = c.item_code
WHERE b.price_code = '1'
ORDER BY a.item_code DESC;
In the clause
WHERE
you can use any of the flow control functions , but I don't think you need it there. Better do aLEFT JOIN
:As I understood with your explanation what you need is to show the data of the table
moves
and the columnlist_price
of the tableitems_price
if itprice_code
is 1, if it is 2 instead show the columncost_price
of the tablecosts
, something like that?If so, try this:
If it's not what you need, fix your example that even the
trx_num
don't match, so the community can help you better and faster.If I understood your question correctly, you want the following:
items_price
withitems_price.price_code = 1
display value ofitems_price.list_price
.costs.cost_price
.In that case I use
LEFT JOIN
to link tables by fillingNULL
the fields of the linked tables that do not have associated records with a.By doing it like this, when there is no relationship with the table
items_price
with the given condition , ititems_price.list_price
will be validNULL
.With a
IF
we can detect this case and display the value ofcosts.cost_price
in that case:You have an example online at this link .
When I say that in your example the data is wrong, I mean the case of the article
a
: the values initems_price.list_price
andcosts.cost_price
are7
, but you put4
.