Selecting Rows matching 2 or more Column Values?

[Xyphos]

Greetings, first time here, long time coder.

 

I'm making an informational website about a video game I play often in my (many hours of) spare time, consisting of all of the items in game. I've even made a custom application to rip the items data right out of the game's database file and write .sql files for my PHP updating tool to use.

 

I have 3 tables: Items, Attributes, Requirements

 

 

Table Items: ( Patch*, ID*, Name )

Table Attributes: ( Patch*, ID*, AttrNum, AttrVal )

Table Requirements: ( Patch*, ID*, StatNum, StatVal )

 

now, I can successfully LEFT JOIN Attributes and Requirements to Items using the indexes (Patch, ID)

but I need to fetch rows that match 2 column values from Attributes and/or Requirements.

 

I've tried so many query patterns to no avail; either I get every single item returned (BAD!) or only a few matching items while it omits other items that *should* be included.

 

Please help.

--Thanks

[rhodesa]

you probably need to do a left join twice to the same table...can you give a sample set of data and the desired results?

 

off the top of my head though, try this:

SELECT i.* 
   FROM Items i
      LEFT JOIN Attributes a1 ON i.Patch = a1.Patch AND i.ID = a1.ID
      LEFT JOIN Attributes a2 ON i.Patch = a2.Patch AND i.ID = a2.ID
   WHERE a1.AttrNum = 3 AND a1.AttrVal = 'foo' AND
      a2.AttrNum = 5 AND a2.AttrVal = 'bar'

[Xyphos]

Well, due to the table structure which is a SQL version of the game's database format,

the Attributes and Requirements tables consist of many, many entires for the same item, and there are over 125k items in game...

 

but here's a dump from phpmyadmin for 1 item

 

 

***Items Table***
Patch 	ID 		ItemType 	ItemName
17010003 	21605 	9 		Bullets

*** Attributes Table ***
Patch 	ID 		AttrsUID 	AttrsNum 	AttrsVal 	
17010003 	21605 	1	 	76 		0 		
17010003 	21605 	2 		30 		517
17010003 	21605 	3 		0 		-2143288317
17010003 	21605 	4 		212 		100 	
17010003 	21605 	5 		12 		26160 	
17010003 	21605 	6 		79 		26690 	
17010003 	21605 	7 		54 		1 	
17010003 	21605 	8 		74 		25 	
17010003 	21605 	9 		211 		200 	
17010003 	21605 	10		420		2 	
17010003 	21605 	11 		2		20 	
17010003 	21605 	12 		88		9 	
17010003 	21605 	13 		298 		0 	


*** Requirements Table ***
Patch 	ID 	       StatNum 	StatVal
17010003 	21605 	60 	 	1 
17010003 	21605 	18 	 	808 	
17010003 	21605 	16 	 	662 	
17010003 	21605 	389 	 	2 	
17010003 	21605 	60 		1 	
17010003 	21605 	16 	 	659 	
17010003 	21605 	18 	 	804 	
17010003 	21605 	389 	 	2 	

 

also, some Values in the Attributes and Requirements tables are bitflags, depending on the Number that references it; I've decoded most of the values to definitions, I know Nums (0,30,298) are in fact bitfield values; so I need to be able to search these rows using bitwise operators.

 

 

so, lets say my site's item search page wants to find the above single item, where

Attributes.Num = 298 AND ( Attributes.Val & 8 )

 

my search engine currently returns all items, because every item has an entry that matches (Attributes.Num = 298)

and (Attributes.Val & 8 )  would match on a totally different row.