mysql query question (images,tags)
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 ) But it doesnt´t work :( It would be awesome if somebody could help me. thanks a lot! -jens -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query question (images,tags)
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]
MySQL query question
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]
Fw: MySQL query question
Oops, I meant to copy the mailing list on this reply. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Josh Mellicker [EMAIL PROTECTED] Sent: Saturday, December 24, 2005 9:24 AM Subject: Re: MySQL query question - Original Message - From: Josh Mellicker [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, December 24, 2005 5:23 AM Subject: MySQL query question 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. I'm a bit confused by the wording of your question, especially the first sentence: it seems to be implying that you have actually implemented direct many-to-many relationships in your database. That is a very rare thing if you've done it. Normally, each many-to-many relationship is broken down into two one-to-many relationships with another table, called an association table or intersection table, between them. Have a look at this item - http://lists.mysql.com/mysql/176918 - from the archives where I explained how this works several months ago. After my remarks about splitting names into first and last name columns, you'll find a discussion of how a many-to-many relationship is normally implemented in a relational database. Now, having said that, your table names suggest that you are already aware of the normal practice of creating association tables and simply described it strangely. That is why I'm confused: I don't know what you've actually done. This is compounded by the fact that you haven't supplied layouts of the table. I find that knowing the names, primary keys and foreign keys of each table and the full definition of each column in each table help a great deal in verifying that the table is correctly designed. I also find it very useful to see a few sample rows of each table so that I can visualize the data better. But you haven't done any of that so I have to operate blind. I'm going to guess that your remark about many-to-many joining tables simply means association tables and that you simply forgot the correct terms. I'm also going to assume that you've designed your tables correctly. I'm having trouble visualizing it properly 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: Users (PK=Userid) === Userid LastNameFirstName --- 1Jones Fred 2Smith Mary Teams (PK=TeamName) TeamNameTeamCity ---- Orioles Baltimore CardinalsSt. Louis Projects (PK=ProjectNo) = ProjectNoProjectDescription ---- A Build new stadium B Raise money for charity Users_Teams (PK=TeamName, Userid) (FKs: User_Teams.TeamName-Teams.TeamName; UserTeams.Userid-Users.Userid) = TeamName Userid - --- Orioles2 Cardinals1 Teams_Projects (PK=TeamName, ProjectNo) (FKs: Teams_Projects.TeamName-Teams.TeamName; Teams_Projects.ProjectNo-Projects.ProjectNo) = TeamNameProjectNo ---- CardinalsA OriolesB OriolesA Now, if you want to join all of these five tables together you will need _at least_ FOUR different joining conditions if you want to avoid getting duplicate or inappropriate rows. Remember, whenever you join N different tables together, you always need at least (N-1) different joining conditions. This is probably why your query isn't working (assuming I am even somewhat close to how your data is structured): you only have THREE joining conditions. With this data, I would do the joins as follows: Users-Users_Teams; Users_Teams-Teams; Projects-Teams_Projects; Teams_Projects-Teams The query would end up looking something like this: SELECT distinct u.LastName FROM users u JOIN users_teams ut on u.Userid = ut.Userid JOIN teams t on ut.TeamName = t.TeamName JOIN teams_projects tp on t.TeamName = tp.TeamName JOIN projects p on tp.ProjectNo = p.ProjectNo WHERE p.ProjectNo = 'A'; or, if you absolutely insist on the old-style syntax, like this: SELECT distinct u.LastName FROM users u, users_teams ut, teams t, teams_projects tp, projects p WHERE p.ProjectNo = 'A' AND u.Userid = ut.Userid AND ut.TeamName
Re: MySQL query question
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
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]
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]
RE: MySQL query question
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]
mysql query question
How can I delete from a table the results of a join query.. I.e, join two tables together, get the resutls, now want to delete that data from one of the tables... Do I have to create a tempory table, hold the data, and delete from the one table data matching in the temp table, or can I just combine a delete statement with my original query...? here is my query, which works.. select distinct table1.ticker_name from table1 LEFT JOIN on table2 on (table1.ticker_name = table2.ticker_name) where table2.ticker_name IS NULL; Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Fwd: mysql query question]
Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Taylor, as far as I see from the documentation and own trials you can just delete from table where conditions_of_this_table. So you can not delete values matching a temp table. But I think I have a possible workaround for you: 1: create a new table with the opposite of your question, i.e. the rows both tables have in common: -- create table interim select table1.* from table1, table2 where table1.ticker_name = table2.ticker_name; 2. delete table1 3. rename table interim to table1 I think that will do what you want. Bye Sabine Taylor Lewick wrote: How can I delete from a table the results of a join query.. I.e, join two tables together, get the resutls, now want to delete that data from one of the tables... Do I have to create a tempory table, hold the data, and delete from the one table data matching in the temp table, or can I just combine a delete statement with my original query...? here is my query, which works.. select distinct table1.ticker_name from table1 LEFT JOIN on table2 on (table1.ticker_name = table2.ticker_name) where table2.ticker_name IS NULL; Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
