Re: Implement one statement w/o subqueries.

[Ruslan U. Zakirov]

Mike Johnson wrote:
From: Ruslan U. Zakirov [mailto:[EMAIL PROTECTED]


                Hello.
Table 1:
Items
id, Name
Table 2:
Properties
id, Item, Name, Value
I want select Items _and_ all thier props only if Item have specified 
property.

Example:
Table Item:
1, Mouse
2, Monitor
3, Keyboard
Table Properties:
1, 1, Color, Red
2, 2, Color, Gray
3, 1, Interface, Wireless
4, 2, MaxResolution, [EMAIL PROTECTED]
5, 3, Color, Gray
6, 3, NumberOfKeys, 101
I want select all Items and all thier properties if item's 
Color is Gray:
Item, Name, Prop, Value
2, Monitor, Color, Gray
2, Monitor, MaxResolution, [EMAIL PROTECTED]
3, Keyboard, Color, Gray
3, Keyboard, NumberOfKeys, 101

I hope you've understand what I mean.
                Thanks. Ruslan.
PS: MySQL 4.0.x


I believe you just need to join the Properties table twice:

SELECT I.id, I.Name, P2.Name, P2.Value 
FROM Properties P1 
INNER JOIN Items I ON I.id=P1.Item 
INNER JOIN Properties P2 ON P2.Item=I.id 
WHERE P1.Name='Color' 
AND P1.Value='Gray';


Thanks for reply.
This solution is right, was my fault used wrong alias in result set.
		Best regards. Ruslan.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]