SQLite DISTINCT with ID

[jfxwave]

sQuery = "Select DISTINCT <Column> FROM <Table> order by <Column>";


How would i get the ID number from this.

I'm trying to list all the <Column> without no duplicates and i need the ID of everyone that it lists.

I would type everything i tried but i don't have 4 hours.


Any help would be appreciated.

Thanks
Jean

[azmanar]

sQuery = "Select DISTINCT <Column> FROM <Table> order by <Column>";

How would i get the ID number from this.
I'm trying to list all the <Column> without no duplicates and i need the ID of everyone that it lists.
I would type everything i tried but i don't have 4 hours.
Any help would be appreciated.
Thanks
Jean

Hi Jean,

DISTINCT has some shortcomings unless u just want to find rows from only a single column or want to JOIN / UNION results with another table. It also takes up system and SQL resources.

Try this one:

sQuery ="SELECT id,email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )";

[jfxwave]

azmanar:

Thanks for the help but it didn't work so i played around with it and

sQuery ="SELECT ID,UnitMake FROM Unit GROUP BY UnitMake";

worked.

What i'm trying to do is

Table = Unit

I have ID, UnitMake, UnitModel
After someone enters info it might look like this
ID = 1, 2, 3, 4, and so on
UnitMake = Compaq, Dell, Dell, Compaq
UnitModel = SR100, D2000, D3000, SR2100

What i'm trying to do is have ComboBoxes where when you pick Compaq everywhere UnitMake has Compaq it will list the UnitModel (on the same row) in another ComboBox.

I hope there is a easy way of doing this but i can't seem to find it.

Thanks again for the help i did learn from your post.
Jean

[azmanar]

Jean,

I can't say it's fairly simple.

ComboBox1 <<< List of brands
ComboBox2 <<< List all the models of each brand that was selected.

If the above is correct, here's what I would do.

My scheme:

1. On Show, I populate ComboBox1.

sBrandQuery = "SELECT DISTINCT unitMake FROM unit ORDER BY unitMake ASC";
tBrandQuery = SQLite.QueryToTable(db,sBrandQuery);

--Disable Updating
ComboBox.SetUpdate("ComboBox1", false);
ComboBox.ResetContent("ComboBox1");

if tBrandQuery and tBrandQuery.Rows > 0 then

for i=1,tBrandQuery.Rows do
sUnitMake = tBrandQuery.Data[i]["unitMake"];
ComboBox.AddItem("ComboBox1", sUnitMake, sUnitMake);
end --forLoop

else
ComboBox.AddItem("ComboBox1", "DB EMPTY", "");
end --endIfQueryNotNull

--Allow display the updated content
ComboBox.SetUpdate("ComboBox1", true);

2. On Select ComboBox1, I send the selected variable value to sQuery.

sBrand = ComboBox.GetSelected("ComboBox1")
sQuery = "SELECT unitModel FROM unit WHERE unitMake =' "..sBrand.." ' ";
tQuery = SQLite.QueryToTable(db,sQuery);

3. Once the query is completed, I will populate ComboBox2.

--Disable Updating
ComboBox.SetUpdate("ComboBox2", false);
ComboBox.ResetContent("ComboBox2");

if tQuery and tQuery.Rows > 0 then

for i=1,tQuery.Rows do
sUnitModel = tQuery.Data[i]["unitModel"];
ComboBox.AddItem("ComboBox2", sUnitModel, sUnitModel);
end --forLoop

else

ComboBox.AddItem("ComboBox2", "NOT LISTED", "");

end --endIfQueryNotNull

--Allow display the updated content
ComboBox.SetUpdate("ComboBox2", true);

---------beep beep---------------------

Try it out...

[jfxwave]

Oh my god i can't believe you took the time to write or copy and paste all that.

As soon as i get home i will start working on it and let you know.


Thanks a million azmanar.

Jean

[azmanar]

Oh my god i can't believe you took the time to write or copy and paste all that.

As soon as i get home i will start working on it and let you know.


Thanks a million azmanar.

Jean

I learnt this method from Worm and Tigger.... My coding is still very crude though coz I'm just a slow learner. But I still share what I know.

Hope it works for you... or maybe you can tweak till it works.

Just remember to check for errors after every SQLite query using Application.GetLastError() function.

[azmanar]

Jean,

I skipped an important ACTION in the previous code in Note 2. Here is the
correct one.

2. On Select ComboBox1, I send the selected variable value to sQuery.

nBrand = ComboBox.GetSelected("ComboBox1")
sBrand = ComboBox.GetItemText("ComboBox1", nBrand); --- this was missing
sQuery = "SELECT unitModel FROM unit WHERE unitMake =' "..sBrand.." ' ";
tQuery = SQLite.QueryToTable(db,sQuery);

-----------------------------------
It should work. Anyhow, I am about to uplaod a sample on my site called:
DB2COMBO.

[jfxwave]

Works perfect...

The only thing i had to do was

sQuery = "SELECT unitModel FROM unit WHERE (unitMake =' "..sBrand.." ') ";

Thanks so much..


Jean

[azmanar]

Jean,

Good to know it works fer ur project...

[jfxwave]

Yes it works perfect.

One more question if i can please.

How would i delete them or how would i delete a row based by what UnitMake was. ( like: sQuery = "SELECT unitModel FROM unit WHERE unitMake =' "..sBrand.." ' " but it would be delete and i would want to delete all rows that unitMake has ..sBrand...


Thanks again for all the help

Jean

[jfxwave]

I worked it out.

Thanks for all your help..