Need help with MySql Query

Hi,

I need little help with the query.
i.e I've following  table with products data:
id, store_id, title, price, is_store_own_product

Followng is the scenerio:

Stores can have their own products as well as other products. With each product we have boolean field "is_store_own_product" which shows its store own product also there can be 1 store selected all the time:

Now, I need to show all products for selected store i.e where (store_id = current_store_id AND is_store_own_product=0 or is_store_own_product=1) but also want to show other store products that are not their own i.e where (store_id <> current_store_id AND is_store_own_product=0).

I tried with "NOT IN" but its taking lot of time.

I hope this make sense.

Thanks a lot.
LVL 2
Imran AliWeb DeveloperAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
Select id, store_id, title, price, is_store_own_product
From tblProduct Where (store_id = @store_id) or  (store_id <> @store_id and is_store_own_product = 0) 

Open in new window


@store_id is store_id you are searching for.

Also try:
Select id, store_id, title, price, is_store_own_product
From tblProduct Where (store_id = @store_id) or  (is_store_own_product = 0) 

Open in new window

0
 
arnoldCommented:
How are you looking to distinguish the items? It is less an issue if a query and more in the display
Presumably you are using php or something similar the point is during the creation of a response, you could alter/distinguish how the product is listed to make it clear which is a store item and which is not.
This distinction can not be achieved on the MySQL level as it only returns data. The display/rendering of the data to the user whether it is a buyer via a web page, or an employee checking whether an item a customer wants is available in store or has to ve ordered/wait fir it to be shipped in..
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
To address the good point from Arnold, Include an alias column [Location] using the following:

Select id, store_id, title, price, is_store_own_product , Case store_id when @store_id Then 'This Store' Else 'Other Stores' End [Location]
From tblProduct Where ...
Order By [Location], id

Use where statements discussed before.



Mike
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Imran AliWeb DeveloperAuthor Commented:
@Mike.
Thanks a lot .

@Arnold
I wanted to filter results not display issue.
Thanks.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Imran,

Did you use the first WHERE clause or the second one?

Thanks
0
 
arnoldCommented:
glad mike's answer helped you.
0
 
Imran AliWeb DeveloperAuthor Commented:
@Mike

Used first where clause.

select id, store_id, title, price, is_store_own_product
From tblProduct Where (store_id = @store_id) or  (store_id <> @store_id and is_store_own_product = 0)

Open in new window

1
 
Mike EghtebasDatabase and Application DeveloperCommented:
Thank you for the response.
0
All Courses

From novice to tech pro — start learning today.