RE: Join issues
It looks like that your first join clause are inconsistent in itself, that is, you declared it for tables A B, but actually used A C instead: ts_software RIGHT JOIN t_computers ON t_softwareassoc.SoftwareAssocSoftwareID = ts_software.SoftwareID -Original Message- From: Weydson Lima [mailto:weys...@gmail.com] Sent: Wednesday, April 21, 2010 10:51 AM To: mysql@lists.mysql.com Subject: Join issues I am dealing with a JOIN error issue. The following query: SELECT * FROM (((ts_software RIGHT JOIN t_computers ON t_softwareassoc.SoftwareAssocSoftwareID = ts_software.SoftwareID) LEFT JOIN ts_softwaremfg ON ts_software.SoftwareMfgID = ts_softwaremfg.SoftwareMfgID) LEFT JOIN ts_eqtype ON t_computers.CompEqTypeID = ts_eqtype.EqTypeID) RIGHT JOIN t_softwareassoc ON t_computers.CompID = t_softwareassoc.SoftwareAssocCompID is giving the error: C_ReportUnknown column 't_softwareassoc.SoftwareAssocSoftwareID' in 'on clause' I assume that the reason for that error is because the first join is being called before the RIGHT JOIN t_softwareassoc. What would be the best way to solve that? I tried to move the RIGHT JOIN t_softwareassoc to the top but it didn't help. Thank you, Wey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: basic SQL question
How I can get the right one? Depending on your goal, which was not given here. I recall in my earlier career, in the computing center there was a banner saying: I wish they could sell this computer: It never does what I want it to do, only what I tell it to do. The result you got was intrinsic to your data. I'd suggest you take a closer look at your data, do a hand calculation for each step in your where-clause. Then you'd see the reason of the returned results, and perhaps find a way to achieve what you want. Best regards, Lin -Original Message- From: Gomez Fabre, Pedro Manuel [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 5:19 AM To: [EMAIL PROTECTED] Subject: basic SQL question Dear all, I have the following problem, I am trying to select records from two tables. the tables are constructed like: block block_id sequence_id snp_required first_polymorphism_index last_polymorphism_index first_reference_positio last_reference_position start_pos end_pos tiled_bp polymorphism_block_map block_id polymorphism_index polymorphism_id When I try to do: mysql select first_polymorphism_index,last_polymorphism_index from block where first_polymorphism_index like 14163 OR first_polymorphism_index like 14750 OR first_polymorphism_index like 14932; +--+-+ | first_polymorphism_index | last_polymorphism_index | +--+-+ |14163 | 14235 | |14750 | 14802 | |14932 | 14980 | +--+-+ 3 rows in set (0.02 sec) everything works fine, easy ;) but if I try to link these two tables I got: +--++-+- ++-+ | first_polymorphism_index | polymorphism_index | polymorphism_id | last_polymorphism_index | polymorphism_index | polymorphism_id | +--++-+- ++-+ |14163 | 14163 | P0216196 | 14235 | 14235 | P0216288| |14750 | 14750 | P0217007 | 14235 | 14235 | P0216288| |14932 | 14932 | P0217251 | 14235 | 14235 | P0216288| +--++-+- ++-+ 3 rows in set (1 min 27.48 sec) ## # this does not return the right values on the las polymorphism index ## SELECT b1.first_polymorphism_index, p1.polymorphism_index, p1.polymorphism_id, b2.last_polymorphism_index, p2.polymorphism_index, p2.polymorphism_id FROM block as b1, polymorphism_block_map as p1, block as b2, polymorphism_block_map as p2 WHERE b1.first_polymorphism_index = p1.polymorphism_index AND b2.last_polymorphism_index = p2.polymorphism_index LIMIT 5; But the second polymorphism_index is always the same, How I can get the right one? Thanks in advance P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested SELECT statements problem
One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicating records
Karam If your installed MySQL version supports sub-query, try this: Delete your_table t where (t.email, t.version) not in ( select s.email, min(s.version) from your_table s group by s.email ) Hope this helps. Lin -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 11:27 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Deleting duplicating records Well that is OK if I have only one email. What if if I have thousands of users duplicated... Do I need to write SQL query 1000 times Karam --- Jeff McKeon [EMAIL PROTECTED] wrote: How bout Delete from tablename where email like [EMAIL PROTECTED] ID 1 Jeff -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:33 AM To: [EMAIL PROTECTED] Subject: Deleting duplicating records Greetings I manage a website wherein i keep track of the people email who have downloaded my software and the version number. the structure is like - id int auto_increment primary key, email char, version now the same person can download different version therfore my table has data like this - 1,[EMAIL PROTECTED],1.0 2,[EMAIL PROTECTED],2.0 3,[EMAIL PROTECTED],3.0 Now I want to delete all the records wherein all rows with duplicate email addresses are deleted so that i have data like 1,[EMAIL PROTECTED],1.0 ... What should be the query? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT problem
Hi, Rachel, It seems most people have missed this message. Since you didn't give enough information in your question, in order to answer your question, I need to make up some assumptions, which might or might not be correct :-( Suppose the same favsub could appear in either or both tables year9 and year10. (From an overall DB design point of view, it would be better off not to create distinct tables for distinct years, but have one single table with an additional year column -- with this alternative design, there would be no need to create a new table each year and no need for this question.) Without being able to build your tables locally to test it (thus have no 100% certainty -- apology), I suggest the following sequence of 4 SQL statements that use a temporary table, with the same structure, to first merge year9 and year10 data to make up for your separate-year table design: Create temporary table X (favsub int, sex char(1), . ) ; Insert into X ((select favsub, sex, . from year9 ) union all (select favsub, sex, . from year10 ) ) ; Select a.favsub, count(m.sex)/count(a.*)*100, count(f.sex)/count(a.*)*100 from X a, X m, X f where a.favsub = m.favsub and a.favsub = f.favsub and m.sex = 'm' and f.sex = 'f' order by a.favsub ; -- you might need to play around to format the query result to meet your needs. After executing the query, you can then issue the 4th SQL to drop temporary table X. Hope this helps. Best regards, Lin -Original Message- From: Rachel Cunliffe [mailto:[EMAIL PROTECTED] Sent: Sunday, July 27, 2003 9:00 PM To: [EMAIL PROTECTED] Subject: SELECT problem Hi, I'm new at complex SELECT statements, so any help would be appreciated. I need to create a summary table of counts from two tables in the database: year9 has a stack of variables including sex and favsub (favourite subject) year10 also has a stack of variables including sex and favsub I'd like to output a table with the following (column %'s if possible, otherwise counts): sex favsub malefemale 1 10% 5% 2 ... ... At the moment, I have this as my MYSQL query: SELECT favsub, sex, COUNT(favsub) FROM year9 GROUP BY favsub,sex ORDER BY favsub,sex * Problems: this is only for one of the tables, and also it's quite messy formatting it to a nice HTML layout as there are possibly two rows for each favourite subject, they aren't on the same row. It's also outputting the counts, not percentages so I need to do another query to figure out the total number of males/females. Again, any help appreciated. Kind regards Rachel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design decision
Lian, Between your design solutions (1) and (3), you need to decide, from the logical business requirement, whether the nature of the relationship between user and group is one-to-many (a group may have many users, and each user may belong to exactly one group) or many-to-many (a group may have many users, and each user may belong to multiple groups). For the former, use Solution (3), for the latter, use Solution (1). Granted, Solution (3) is a subset of Solution (1), but requires more resources which might be a waste if you only need represent a one-to-many relationship. Your solution (2) has no restriction on the granularity of the relationship i.e., it can support both; it all depends on your implementation outside SQL, thus is not really a DB schematic means. In this case, the relationship is actually interpreted and maintained by your application program, not by DBMS. In making a choice between Solution (2) and the other two you need to consider the performance difference and code maintenance. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:22 AM To: mysqllist Subject: Design decision Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql desc users; mysql desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is How to store BEST the relations between users and groups?. Solution 1. I use a separate table with this structure: mysql desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user -- group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the group membership of a user so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net I'm not mad. I've been in bad mood for the last 30 years... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicating records
Try this: Delete your_table t Where t.version min(t.version) Group by t.email Lin -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 11:27 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Deleting duplicating records Well that is OK if I have only one email. What if if I have thousands of users duplicated... Do I need to write SQL query 1000 times Karam --- Jeff McKeon [EMAIL PROTECTED] wrote: How bout Delete from tablename where email like [EMAIL PROTECTED] ID 1 Jeff -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:33 AM To: [EMAIL PROTECTED] Subject: Deleting duplicating records Greetings I manage a website wherein i keep track of the people email who have downloaded my software and the version number. the structure is like - id int auto_increment primary key, email char, version now the same person can download different version therfore my table has data like this - 1,[EMAIL PROTECTED],1.0 2,[EMAIL PROTECTED],2.0 3,[EMAIL PROTECTED],3.0 Now I want to delete all the records wherein all rows with duplicate email addresses are deleted so that i have data like 1,[EMAIL PROTECTED],1.0 ... What should be the query? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
Nick, As you stated, your priority field datatype is varchar, with possible values Hi, Medium and Low, as opposed to being integers. The use of max function, as suggested by some colleagues without knowing exactly the datatype would work correctly only on columns of datatype integer. In your case, for textual columns, lexicographic (dictionary) ordering will be used in computing function max, and Medium would win the competition, instead of Hi -- which actually has the lowest ranking in the lexicographic ordering. Perhaps you could use the CASE WHEN ... constructs to map your textual priority into numeric (integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max function to the integer values to get correct results. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 4:41 PM To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: SQL Help... I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: DB creation
Situation: I just installed MySQL 4.0.14 on my Windows XP, the very first time. The server daemon seems started running well. Now, I want to create a new database. However, in the WinMySqlAdmin 1.4 tool, upon right clicking, in the dropdown list there are no create database, create ... items, only flush table, flush ... items. So I could not create a new database, nor tables. Question: (1) what could be the problem? -- could that be certain configuration problems? And (2) any suggestions to overcome/resolve it so that I could start creating new database and tables? Many thanks! Best regards, Lin
