Re: Two Tables Comparator
Rich, it looks to me like your SQL should work - I've never used '!IN', always used 'NOT IN' instead, but that's not to say it won't work. I do note that you're missing the join criteria for your tables classes, signups ... Am I misunderstanding your question? Dan On 10/5/06, Rich [EMAIL PROTECTED] wrote: Hi folks. Any suggestions on the following? If a student signs up for classes, I don't want them viewing those classes so they can choose them again. So I want to take out their 'signups' from the master class list. select classnumber, classnumbersignup from classes, signups where classnumber !IN (select classnumbersignup from signups where userid = [$myID]) and mytype = 'yellow'; I don't want to have to rely on middleware for this. Cheers -- 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]
RE: two tables with same field names into one table
To select the contents of both into one table, you most likely want to use the 'UNION' operator: SELECT * FROM desktops UNION SELECT * FROM laptops If you create the computers table before hand (you can see how you would create either of the others with SHOW CREATE tablename), then you can just do one INSERT into the new table, using a select similar to the one above. Thanks, Matt -Original Message- From: Brad Tilley [mailto:[EMAIL PROTECTED] Sent: 03 April 2004 21:00 To: [EMAIL PROTECTED] Subject: two tables with same field names into one table Hello, I am a mysql newbie. Recently, I've been given the task of joining two tables within the same DB into one table. Currently, the tables are named 'desktops' and 'laptops'... ultimately, I would like one table named 'computers' Both tables have the exact same fields... they fields even have the same names. I tried this: create table computers select * from desktops, laptops where desktops.field_1 = laptops.field_1 ... ... ... But I got an error about duplicate field names. Any suggestions on how to do this? Thanks, Brad -- 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]
Re: two tables with same field names into one table
Thank you Matt, I am using Mysql 3.23.58 on RH Linux 9... UNION isn't supported on this version. On Saturday 03 April 2004 15:20, Matt Chatterley wrote: To select the contents of both into one table, you most likely want to use the 'UNION' operator: SELECT * FROM desktops UNION SELECT * FROM laptops If you create the computers table before hand (you can see how you would create either of the others with SHOW CREATE tablename), then you can just do one INSERT into the new table, using a select similar to the one above. Thanks, Matt -Original Message- From: Brad Tilley [mailto:[EMAIL PROTECTED] Sent: 03 April 2004 21:00 To: [EMAIL PROTECTED] Subject: two tables with same field names into one table Hello, I am a mysql newbie. Recently, I've been given the task of joining two tables within the same DB into one table. Currently, the tables are named 'desktops' and 'laptops'... ultimately, I would like one table named 'computers' Both tables have the exact same fields... they fields even have the same names. I tried this: create table computers select * from desktops, laptops where desktops.field_1 = laptops.field_1 ... ... ... But I got an error about duplicate field names. Any suggestions on how to do this? Thanks, Brad -- Brad Tilley Coordinator of Computer Services 311-D Cassell Coliseum Phone: 540-231-7539 Cell: 540-998-5903 Fax: 540-231-3445 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two tables with same field names into one table
* Brad Tilley create table computers select * from desktops, laptops where desktops.field_1 = laptops.field_1 ... Thank you Matt, I am using Mysql 3.23.58 on RH Linux 9... UNION isn't supported on this version. You can do it in two steps: CREATE TABLE computers SELECT * FROM desktops; INSERT computers SELECT * FROM laptops; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two tables with same field names into one table
There are two ways of handling this, the long way and the short way. The long way is to prefix each ambiguously named column with the full table name, as you have been doing. That *should* have worked for you since that seems to be what you are doing in your example. Or did you literally write laptops.field_1 in your query when the column names were laptops.hard_drive_size and laptops.memory_size or whatever? Remember, you have to prefix every ambiguously named column in the query with its table name, even if that ambigous column name occurs in the SELECT clause, the FROM clause, the WHERE clause, the GROUP BY clause, the HAVING clause, or the ORDER BY clause. Maybe you removed ambiguities in the WHERE but not the ORDER BY or some other clause? The short way, and probably the most widely seen way, is to use a sort of temporary variable to serve as a substitute for the table name. For example, given the following tables: Laptops = hard_drive_size memory_size CPU Desktops == hard_drive_size memory_size CPU You could write a query like this: select l.hard_drive_size, d.hard_drive_size, l.memory_size, d.memory_size from laptops as l, desktops d where l.CPU = d.CPU order by l.memory_size; The query I've just stated doesn't make a whole lot of sense in terms of what it means but it illustrates how to use the as clause to make a temporary alias for the table name and how to use these aliases in the rest of the query. I think that answers the gist of your question. I have one qualm about your note though. If you are talking about combining two tables that have the exact same columns, you aren't really joining them. The word join has a fairly narrow meaning in SQL. Normally, we join tables that are different from one another but that have one (or sometimes more) things in common. I don't know if you've had any data normalization yet - First, Second, and Third Normal Form - but most joins (in the SQL sense) are between two tables that have a parent-child relationship with a primary key/foreign key. For example, one table may list employees with one row per employee while another table lists departments with one row per department. If each employee belongs to one and only one department but a department has potentially many different employees, this is said to be a one-to-many relationship. Typically, we'd store the employee's department number in the employee table; the department number would be a primary key in the department table and a foreign key in the employee table. Then, if you wanted a report showing departments and the people who work in them, you would join the department and employee tables on the thing they have in common, the department number. The two tables would likely have nothing else in common. That would be a classic example of a join in a relational database. The situation where you describe two tables with the same columns is more likely to be a case where you'd combine the tables via a UNION. Basically, you would interleave them into a single table. Think of two decks of cards, one with red backs and one with blue backs. A UNION is the same idea as shuffling those two decks together. The end result, of course, is that you'd end up with a single deck of cards, some with red backs and some with blue backs. That is quite a different situation than joining. Sorry to go into lecture mode but its a rather important distinction in relational databases. Rhino - Original Message - From: Brad Tilley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, April 03, 2004 3:00 PM Subject: two tables with same field names into one table Hello, I am a mysql newbie. Recently, I've been given the task of joining two tables within the same DB into one table. Currently, the tables are named 'desktops' and 'laptops'... ultimately, I would like one table named 'computers' Both tables have the exact same fields... they fields even have the same names. I tried this: create table computers select * from desktops, laptops where desktops.field_1 = laptops.field_1 ... ... ... But I got an error about duplicate field names. Any suggestions on how to do this? Thanks, Brad -- 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]
Re: two tables
debug, is there any difference between: a) select lname, fname from person_tb, details_tb where mem_id = det_id and fin = y; and b) select lname, fname from person_tb where mem_id in (select det_id from details_tb where fin = y) Yes, the first does give error free output. The second says ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select det_id from details_tb where fin = y)' at line 3 which one will execute faster ? The first. Regards Trevor Rhodes === Powered by Linux- Mandrake 9.2 Registered Linux user # 290542 at http://counter.li.org Registered Machine #'s 186951 Mandrake Club Silver Member Source : my 100 % Microsoft-free personal computer. === 16:10:15 up 38 min, 1 user, load average: 1.00, 1.07, 1.06 -- Never mud wrestle with a pig.. you get dirty and the pig enjoys it! Never try to teach a pig to dance. You waste your time and annoy the pig. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two tables
Hello All, person_tb contains mem_id, lname and fname details_tb contains det_id and fin. They both contain other fields, but I don't care about them at this time. I'm trying the following select lname, fname from person_tb, details_tb where mem_id = det_id and fin = y; but it is giving me all 103 rows instead of just the ones I need. How is it to know which ones you need? Add that logic to the where clause. Do you mean like: where person_tb.mem_id = details_tb.det_id and details_tb.fin = y; ? If so, then it gave me the whole 103 as well. An example would be much more useful than a vague description. Cut the data down to a smaller set and show us the problem. person_tb mem_id lname fname 001 Smith Terry 002 Jones Larry 003 Barfbag Harry details_tb det_id fin 001 y 002 n 003 y I'm trying to get Smith and Barfbag to show up instead of all three. Regards Trevor Rhodes === Powered by Linux- Mandrake 9.2 Registered Linux user # 290542 at http://counter.li.org Registered Machine #'s 186951 Mandrake Club Silver Member Source : my 100 % Microsoft-free personal computer. === 16:12:07 up 40 min, 1 user, load average: 1.23, 1.18, 1.10 -- Never mud wrestle with a pig.. you get dirty and the pig enjoys it! Never try to teach a pig to dance. You waste your time and annoy the pig. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: two tables
Trevor Rhodes wrote: Hello All, person_tb contains mem_id, lname and fname details_tb contains det_id and fin. They both contain other fields, but I don't care about them at this time. I'm trying the following select lname, fname from person_tb, details_tb where mem_id = det_id and fin = y; but it is giving me all 103 rows instead of just the ones I need. How is it to know which ones you need? Add that logic to the where clause. An example would be much more useful than a vague description. Cut the data down to a smaller set and show us the problem. Any help here would be most appreciated. Regards Trevor Rhodes === Powered by Linux- Mandrake 9.1 Registered Linux user # 290542 at http://counter.li.org Registered Machine #'s 186951 Mandrake Club Silver Member Source : my 100 % Microsoft-free personal computer. === 19:37:04 up 2 days, 19:26, 1 user, load average: 0.95, 0.58, 0.69 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two tables, which did not match?
Daniel, I have a question about determining which case occurred when there are no matches - using two tables. Scenario: - two tables, one for photos, and one for collections - zero or more photos can belong to a collection - the database may not know the collection_id requested (i.e. the user on the browser side did something to request a non-existent collection) We can hit: a) no collection b) a collection that hasn't had any photos moved/uploaded to it yet, or.. c) a collection with one or more photos (the normal, everything's fine case) Here's the php setting up the query... // shortened version for posting question... // there are many more fields... $sql_str =EOD select photo.photo_id AS photo_id, collection.collection_dir AS collection_dir from photo, collection where photo.collection_id = $collection and collection.collection_id = $collection EOD; $sql_result = $db-query($sql_str); When I check the number of rows returned, and get 0, I have no way (yet) of knowing why (no collection, or just no photos in a collection). Should I be using COUNT/GROUP BY? The first observation is the way you have worded the join - I'm sure it will work when there is both collection and photo data, but to join the photo and collection tables it would be 'normal' to establish the relationship between their keys, eg: WHERE photo.collection_id = collection.collection_id AND ... To use COUNT would presumably necessitate querying the db more than once. How are you going to GROUP the resultset - I assume from the PHP code that you are only asking for details from one collection at a time? An inner join (as per PHP code above) will only return data when there are related rows in both tables [hit (c) above]. An outer join will enable you to find collections without photos [hit (b) above] (ie collection.collection_id = $collection, but with NULL(s) returned from the photo table) - and (theoretically) photos without collection identification [not mentioned above]. Obviously hit (a) is the empty resultset/'non-hit'/zero hit situation. Changing to an outer join will allow you to cover the three (indeed add a fourth) hit-cases, and by analysing the resultset you will be able to distinguish between them. Information about the different types of join is covered in the manual. Beware the left/right wording of an outer join, and for efficiency, word things so that MySQL will examine the table with the fewest likely hits first. Let us know how you get on! =dn - 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
Re: Two tables, which did not match?
At 10:55 AM + 3/29/02, DL Neil wrote: Scenario: - two tables, one for photos, and one for collections - zero or more photos can belong to a collection - the database may not know the collection_id requested (i.e. the user on the browser side did something to request a non-existent collection) The first observation is the way you have worded the join - I'm sure it will work when there is both collection and photo data, but to join the photo and collection tables it would be 'normal' to establish the relationship between their keys, eg: [lots of good analysis] Thanks much, I will study this. A lot of reading last night opened up the wonders of JOIN. I believe that I will pursue a LEFT JOIN (collection first), and a count on a photo_id field. That should give me the cases I'm looking for. I will follow up with my solution in the next day or so. As long as I am writing: has anyone here taken the MySQL class? (http://www.mysql.com/training/curriculum.html) It would seem to be a good, intensive week of training, applicable to other DB's as well. -- Daniel L. Smith - [EMAIL PROTECTED] - Sonoma County, CA AIM: JavaE36M3 ICQ: 12834734 http://resume.daniel.org - 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
Re: Two tables, which did not match?
At 10:55 AM + 3/29/02, DL Neil wrote: We can hit: a) no collection b) a collection that hasn't had any photos moved/uploaded to it yet, or.. c) a collection with one or more photos (the normal, everything's fine case) Let us know how you get on! Thanks again DL, my first stab at using JOIN in the query: select photo.photo_id AS photo_id, photo.keywords AS keywords, collection.title AS title, collection.description AS description from collection LEFT JOIN photo on photo.collection_id = collection.collection_id where collection.collection_id = $collection group by photo.photo_id; I can now assess empty set vs empty collection vs collection with photos. -- Daniel L. Smith - Sonoma County, CA http://resume.daniel.org - 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
