[weirdguy]

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.

[baavgai]

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.

[weirdguy]

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,

[baavgai]

weirdguy, 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.