Solved

Need help with MySql Query

Posted on 2017-06-17
8
20 Views
Last Modified: 2017-06-18
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.
0
Comment
Question by:Imran Ali
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
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
 
LVL 78

Expert Comment

by:arnold
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
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
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 2

Author Closing Comment

by:Imran Ali
@Mike.
Thanks a lot .

@Arnold
I wanted to filter results not display issue.
Thanks.
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
Hi Imran,

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

Thanks
0
 
LVL 78

Expert Comment

by:arnold
glad mike's answer helped you.
0
 
LVL 2

Author Comment

by:Imran Ali
@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
 
LVL 34

Expert Comment

by:Mike Eghtebas
Thank you for the response.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Join & Write a Comment

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question