[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with MySql Query

Posted on 2017-06-17
8
Medium Priority
?
49 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 42181634
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 81

Expert Comment

by:arnold
ID: 42181738
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
ID: 42181774
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 2

Author Closing Comment

by:Imran Ali
ID: 42181792
@Mike.
Thanks a lot .

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

Expert Comment

by:Mike Eghtebas
ID: 42181838
Hi Imran,

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

Thanks
0
 
LVL 81

Expert Comment

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

Author Comment

by:Imran Ali
ID: 42182790
@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
ID: 42182811
Thank you for the response.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

868 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