HOW TO USE SQL 'BETWEEN' FOR DATE VALUE IN 2 SEPERATE COLUMN IN DB

MySQL database
#1

Hi There,

I am working a site project and i am trying to implement a sales period on the items based on sales start date and sales end date. HERE IS THE DB

[php]articles
id name price
1 Gy-shirt 190
2 GX-shirt 200
3 GX-glass 250[/php]

[php]sales
id product_id price start_date end_date
1 1 180 2016-08-23 2016-08-27
2 2 190 2016-08-23 2016-08-30
3 3 230 2016-08-21 2016-08-22[/php]

So i want to select the based on the opening and closing sales date
Here is my query.
[php]
SELECT s.price, CONCAT(shoe.name,’ ‘,shoe.filename,’ ',shoe.price) FROM articles AS shoe LEFT JOIN sales AS s ON (shoe.id = s.product_id BETWEEN s.start_date AND s.end_date )[/php]

But it does not return the sales price of first two articles. Why?

#2

Drop the parentheses, unless you want that kind of grouping? Just add your where clause with the between.

#3

Why are you storing price in two tables? It should only be in one place.

#4

Disagree. This is a situation that warrants it being in two places, though where that second price is may not be the best spot.

#5

[member=72272]astonecipher[/member] i Tried adding the “WHERE” clause WITH “BETWEEN”…but i am having an error message. I am testing my code in phpmyadmin.

#6

[member=46186]Kevin Rubio[/member] the price in the sales table represent the sales price of the article

#7

You need a value for the between. Example, using just the start date.

[php]SELECT s.price, CONCAT(shoe.name,’ ‘,shoe.filename,’ ',shoe.price) FROM articles AS shoe LEFT JOIN sales AS s ON shoe.id = s.product_id where s.start_date BETWEEN ‘your starting date’ AND ‘your ending date’
[/php]

#8

[member=72272]astonecipher[/member] [php]the query[/php] you gave is working fine. I tried to update my question for more details but coud not find the edit button.

Actually what i need is [php]the article with sale price in the sale period should return alongwith article without any sales price in a query[/php].

But with the query you gave…only[php] the articles in the sales period returned[/php]… which is not what i need.
So i hope u see clearly my issue.

Thanks

#9

That phrasing makes this look like homework.

So, based on what I showed, how would you correct the Query to return the required data and no more?

#10

[member=72272]astonecipher[/member] if i have to concat queries but the thing is like i have to select the articles table twice right?

#11

Why do you have to concat anything? 1, it makes it harder to parse exactly what you want, which is why it is rarely used and only then for specific columns. 2, you said you didn’t want the original price. So, if the price is in a concatenating column, it will always be there.

#12

[member=72272]astonecipher[/member] ok i see. i said [php]concat queries[/php] because i am trying to find the [php]right query[/php] to be used…i want to [php]query the article original price[/php] and [php]article saleprice[/php] because i want to show both price so that the customer can the difference.

If the method i use is not correct, could you subject another way of doing it ? …because the thing is that i want to show [php]salesprice of articles between fixed period[/php].

#13

Write out what you want in a pseudo query:

select item number, item file, item name, item price, sale price from table 1 inner join on table 2 using this relation where this date is between this value and this value;

#14
Disagree. This is a situation that warrants it being in two places, though where that second price is may not be the best spot.

How so? The sale price should be a calculation of the actual price which actually could be a calculated markup from the item cost… You don’t store calculated data. What are you gonna do if you have one sale price for a wholesaler, another price for a volume buyer, another price for a valued customer? Are you going to keep adding columns to the DB?

[member=46186]Kevin Rubio[/member] the price in the sales table represent the sales price of the article
Doesn't matter.
#15

What I am referring to is, you store the price in say a products table, then you store the price again in say a line item table.

Lets say you have a 75% off sale and someone buys a $100 item for $25. Now they want to return it but, the sale is over. The line item table keeps track of what the customer actually paid at the time of purchase, so you don’t refund $100 on a $25 purchase.

Sponsor our Newsletter | Privacy Policy | Terms of Service