Re: mysql query question (images,tags)

2006-11-09 Thread Jens Kleikamp 

Jens Kleikamp schrieb:

hi to all,

I have a simple question/szenario.

Here are my tables:

1. image (id, name)
2. tag (id, name)
3. images_tags (image_id, tag_id)

At the moment I have the following working query, it selects all images 
which have *at least one of the tag ids* (25,30) assigned.


SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id  images_tags.tag_id IN (25,30)


Now my plan is to adjust the query so that only images are selected 
which have *all the tags assigned*, so the IN command in the ON clause 
does not fit anymore. Here is my attempt to replace the IN:



SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id 
(
   images_tags.tag_id = 25
   
   images_tags.tag_id = 30
)


Solution #1:

SELECT fgl_image.id, fgl_image.label
FROM fgl_image, fgl_images_tags A, fgl_images_tags B
WHERE fgl_image.id = A.image_id
AND fgl_image.id = B.image_id
AND A.tag_id =10
AND B.tag_id =9

Solution #2:

SELECT fgl_image.id, fgl_image.label
FROM fgl_image
INNER JOIN fgl_images_tags ON fgl_images_tags.image_id = fgl_image.id
AND fgl_images_tags.tag_id
IN ( 10, 9 )
GROUP BY fgl_image.id, fgl_image.label
HAVING COUNT( * ) =2


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

Re: MySQL query question

2005-12-24 Thread Hank 
since I'm not sure how users would
 ever be directly associated with teams - I would have expected to find
 players to be associated with teams - so forgive me if this doesn't
 resemble very much what you're doing:

Think corporate projects, not sports.

Here's my take on the original query.. you don't actually need to use
the teams table in the query, as long as you have DISTINCT in the
Select:

SELECT DISTINCT username
FROM users u, users_teams ut, projects_teams pt , projects p
WHERE p.project_id = '1'
AND pt.project_id = p.project_id
AND ut.team_id = pt.team_id
AND u.user_id = ut.user_id

Also, just a style comment, I would find it confusing just to use id
as the key in the projects, team, and user tables.. and user_id,
team_id, and project_id in the associative tables... the field
names should be consistent throughout, so when reading queries, it's
obvious which id one is talking about.





On 12/24/05, Josh Mellicker [EMAIL PROTECTED] wrote:
 I have several tables, all with many-to-many joining tables.

 users

 users_teams

 teams

 teams_projects

 projects


 ---

 So, with a projects.id = 1, I want to get all the usernames of people
 on teams assigned to that project.

 SELECT DISTINCT username
 FROM users, users_teams, teams, projects_teams, projects
 WHERE projects.id = '1'
 AND projects_teams.project_id = projects.id
 AND teams.id = projects_teams.team_id
 AND users_teams.user_id = users.id

 gives me ALL the users who are on any team... even teams not assigned
 to that project.

 What gives? My brain hurts. Thanks for any help.

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




--

-Hank

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

Re: MySQL query question

2005-12-24 Thread Peter Brawley 

Josh,

I have several tables, all with many-to-many joining tables.
 users
 users_teams
 teams
 teams_projects
 projects

Once again explicit join syntax clarifies matters:

 SELECT DISTINCT username
 FROM users
 INNER JOIN users_teams ON (users.id = users_teams.user_id)
 INNER JOIN teams ON (...you didn't mention these keys...)
 INNER JOIN projects_teams ON (teams.id = projects_teams.team_id)
 INNER JOIN projects ON (projects_teams.project_id = projects.id)
 WHERE projects.id = 1;

PB

-

Josh Mellicker wrote:


I have several tables, all with many-to-many joining tables.

users

users_teams

teams

teams_projects

projects


---

So, with a projects.id = 1, I want to get all the usernames of people  
on teams assigned to that project.


SELECT DISTINCT username
FROM users, users_teams, teams, projects_teams, projects
WHERE projects.id = '1'
AND projects_teams.project_id = projects.id
AND teams.id = projects_teams.team_id
AND users_teams.user_id = users.id

gives me ALL the users who are on any team... even teams not assigned  
to that project.


What gives? My brain hurts. Thanks for any help.




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005


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

RE: MySQL query question

2003-11-07 Thread Andy Eastham 
Chris,

You're almost there!
select * from temp where col2 like concat('%',col1, '%');

Andy

 -Original Message-
 From: Chris A. Mattingly [mailto:[EMAIL PROTECTED]
 Sent: 07 November 2003 17:01
 To: [EMAIL PROTECTED]
 Subject: MySQL query question


 I've searched around on the lists archives and even did some
 googling, but I'm
 having trouble finding the answer to this question.

 Given that I have a table with 2 columns (say col1, col2) I want
 to be able to
 search for the value of col1 in the value of col2.  Let's say that in one
 instance col1 = foo and col2 = foobar, I want to know, but if col1
 = temp and col2 = foobar, I do not want anything returned.

 A query something like:  SELECT col2 FROM table WHERE col2 LIKE '%col1%';

 Any help would be greatly appreciated.

 -Chris


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




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