Jump to content

  • Log In with Google      Sign In   
  • Create Account

We're offering banner ads on our site from just $5!

1. Details HERE. 2. GDNet+ Subscriptions HERE. 3. Ad upload HERE.


#ActualSollum

Posted 28 December 2012 - 01:36 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot

I would advice befriending joins. Main workhorse of RDBM.

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--For example, select all character names that have healing potions, by item id

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.IID = 5

--by name

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
INNER JOIN ITEMS IT ON CHIT.IID = IT.ID
WHERE IT.ITEM_TITLE = 'Healing Potion'

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--Select all character names that have no items overall
SELECT CH.NAME FROM 
CHARACTERS CH
LEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.ID IS NULL

I wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.


#3Sollum

Posted 28 December 2012 - 01:34 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot
I would advice befriending joins. Main workhorse of RDBM.

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--For example, select all character names that have healing potions, by item idSELECT CH.NAME FROM CHARACTERS CHINNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDWHERE CHIT.IID = 5--by nameSELECT CH.NAME FROM CHARACTERS CHINNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDINNER JOIN ITEMS IT ON CHIT.IID = IT.IDWHERE IT.ITEM_TITLE = 'Healing Potion'


--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Select all character names that have no items overall
SELECT CH.NAME FROM CHARACTERS CHLEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDWHERE CHIT.ID IS NULL

I wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.

#2Sollum

Posted 28 December 2012 - 01:34 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot
I would advice befriending joins. Main workhorse of RDBM.

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--For example, select all character names that have healing potions, by item idSELECT CH.NAME FROM CHARACTERS CHINNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDWHERE CHIT.IID = 5--by nameSELECT CH.NAME FROM CHARACTERS CHINNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDINNER JOIN ITEMS IT ON CHIT.IID = IT.IDWHERE IT.ITEM_TITLE = 'Healing Potion'


--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
--Select all character names that have no items overall
SELECT CH.NAME FROM CHARACTERS CHLEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CIDWHERE CHIT.ID IS NULL

I wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.

#1Sollum

Posted 28 December 2012 - 01:33 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot

 

I would advice befriending joins. Main workhorse of RDBM.

 

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

 

--For example, select all character names that have healing potions, by item id

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.IID = 5

--by name

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
INNER JOIN ITEMS IT ON CHIT.IID = IT.ID
WHERE IT.ITEM_TITLE = 'Healing Potion'

 

 

 

--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

 

Select all character names that have no items overall

SELECT CH.NAME FROM 
CHARACTERS CH
LEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.ID IS NULL

 

 

I wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.


PARTNERS