3 Replies - 497 Views - Last Post: 15 July 2011 - 06:12 PM Rate Topic: -----

#1 weirdguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 28-October 09

Custom Fields with Multiple Nested SQL

Posted 15 July 2011 - 02:31 AM

Hello all,

My SQL runs fine but I am not sure whether my SQL code is efficient.

SELECT Customer_ID, Customer_Name, 
(SELECT PriceList_ID FROM qCustomerInfoPlan WHERE qCustomerInfoPlan.Customer_ID = 'A1002' AND qCustomerInfoPlan.ProductCategory_ID = 'BAT') AS Battery,
(SELECT PriceList_ID FROM qCustomerInfoPlan WHERE qCustomerInfoPlan.Customer_ID = 'A1002' AND qCustomerInfoPlan.ProductCategory_ID = 'LUB') AS Lubricant,
(SELECT PriceList_ID FROM qCustomerInfoPlan WHERE qCustomerInfoPlan.Customer_ID = 'A1002' AND qCustomerInfoPlan.ProductCategory_ID = 'FIL') AS Filter 
FROM qCustomerInfoPlan


Note
I am using Access (mdb) as my backend VB.Net Database.
qCustomerInfoPlan is a query from multipe tables from mdb.
Note that, 'A1002' would be replaced with '" textbox.Text "', where it would get from Form1. at my VB.Net code.

Reason for the code
The SQL's results are used at Popup Form2, where it displayed Customer Information such as Name, allocated Price for each Product Category. I created the code so its result is single row and easily allow me to bind the table from DataAdapter to my textboxes.

An example of the Binding code.
txtPlanBat.Text = FixNull(ds.Tables("CustomerInfoPlan").Rows(0).Item(2))


Question
As mentioned earlier, the SQL code works but I felt there could be a shorter version as the WHERE statements are repeated at every Custom Fields.

Thanks you all in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Custom Fields with Multiple Nested SQL

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,758
  • Joined: 16-October 07

Re: Custom Fields with Multiple Nested SQL

Posted 15 July 2011 - 04:25 AM

I'd go with something like:
SELECT a.Customer_ID, a.Customer_Name, 
		min(case when a.ProductCategory_ID = 'BAT' then PriceList_ID else null end) AS Battery,
		min(case when a.ProductCategory_ID = 'LUB' then PriceList_ID else null end) AS Lubricant,
		min(case when a.ProductCategory_ID = 'FIL' then PriceList_ID else null end) AS Filter
	FROM qCustomerInfoPlan a
	WHERE a.Customer_ID = 'A1002'
	GROUP BY a.Customer_ID, a.Customer_Name



In Access ( OMG, never use access ), there might be some kind of IIF thing, but CASE is SQL standard.
Was This Post Helpful? 0
  • +
  • -

#3 weirdguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 28-October 09

Re: Custom Fields with Multiple Nested SQL

Posted 15 July 2011 - 06:00 PM

Hello baavgai,

Thanks you for your time and effort to reply my questions.

Lol, I understand that using Access mdb as back-end database may not be the best solution.

As a Novice, this is my first time experienced with Portable database that do not required installation of SQL Studio in Client/Server Computers. If you have other solution for the Portable Database, kindly share with me.

While for your replied SQL, it is indeed true that Access SQL is not compatible with 'Case'. Still, it had gave me some sort ideas to re-write the SQL.

Thanks you again,
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5881
  • View blog
  • Posts: 12,758
  • Joined: 16-October 07

Re: Custom Fields with Multiple Nested SQL

Posted 15 July 2011 - 06:12 PM

View Postweirdguy, on 15 July 2011 - 09:00 PM, said:

While for your replied SQL, it is indeed true that Access SQL is not compatible with 'Case'.


Like I said, IIF.
e.g.
SELECT a.Customer_ID, a.Customer_Name,
   min(iif(a.ProductCategory_ID = 'BAT',PriceList_ID,null)) AS Battery,
...



In visual studio, Add local database is usually enough to get you something useful. It's actually an evolution of prior app specific databases. Think of it as a mini SQL Server file; which it pretty much behaves like.

Other options include sqlite, which is even more light weight. You find sqlite files on most modern mobile devices, including both iOS and Android. Though, of course, Microsoft will favor it's own embedded database as noted above.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1