Re: Question on Join
Manoj wrote: Dear All, I am trying to join two tables say a b. Both tables have a set of dates. I want to join the tables in such a fashion that I retrieve all dates from table A. If table b has corresponding data (for that date) then it will display it or else will display null. I am pretty sure that I can solve this using left outer join but am not able to get it working hence any help would be appreciated! Try something like this: SELECT A.date,B.date FROM A LEFT JOIN B ON B.date=A.date -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on Join
Dear All, I am trying to join two tables say a b. Both tables have a set of dates. I want to join the tables in such a fashion that I retrieve all dates from table A. If table b has corresponding data (for that date) then it will display it or else will display null. I am pretty sure that I can solve this using left outer join but am not able to get it working hence any help would be appreciated! Cheers Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about JOIN behaviour - I can't figure out how it works
Hi. I searched MySQL manual but I stil don't know how a JOIN works. I would like to avoid diging into sources so I'm posting this mail. I have two tables: - a HUGE table having a row ID_keywords - a SMALL table with keywords and columns ID_keywords and keyword_name I have to select some rows from a HUGE table that have certain keywords (there can be 10.000 or even more results). I can acomplish this in two ways: 1. SELECT some_rows_from_h FROM HUGE h LEFT JOIN SMALL s ON h.ID_keywords=s.ID_keywords WHERE (s.keyword_name='name1') OR (s.keyword_name='name2') OR ... GROUP BY ... 2. First get ID_keywords for name1, name2,... and then do: SELECT some_rows FROM HUGE WHERE (ID_keywords=returnes_ID1) OR (ID_keywords=returnes_ID2) OR ... GROUP BY ... And now the question: - How does the JOIN works? Does it first get IDs from the second table (SMALL) and then use them for the WHERE clause? - Which query from the above examples will be faster (on my test data I get every time the response that the query finished in 0.0s , but I'm now working only on a small set of data and not on read data which aren't available to me right now)? - Is there some diference if I made a join on a join? E.g.: SELECT some_rows FROM table1 LEFT JOIN table2 ON something LEFT JOIN table3 ON something_else WHERE blahblah GROUP BY ... Thanks and regards, Dezo - 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: Mysql SELECT question (LEFT JOIN?)
by now, i hope, you've gotten answers from the sql gurus on the list, so i won't clutter with my humble attempts. your comment about problems with joins indicates that corereader might be of some help to you if you have a windows box for a front end. it will let you do quick point-and-click queries, so you can experiment with fairly complex joins. download it from http://corereader.com it's intended to be a production system, but it's a great teaching tool. it connects to anything, but it especially likes mysql. Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - 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: Mysql SELECT question (LEFT JOIN?)
Eric, Friday, November 15, 2002, 1:36:54 AM, you wrote: EA Assume two tables: EA CREATE TABLE block_ip ( EA datestamp int(11) NOT NULL default '0', EA remote_addr char(15) NOT NULL default '', EA PRIMARY KEY (remote_addr), EA KEY datestamp (datestamp) EA ) TYPE=MyISAM; EA CREATE TABLE brute_force ( EA datestamp int(11) NOT NULL default '0', EA remote_addr char(15) NOT NULL default '', EA remote_user char(35) NOT NULL default '', EA KEY remote_addr (remote_addr), EA KEY datestamp (datestamp), EA KEY remote_user (remote_user) EA ) TYPE=MyISAM; EA Contents of the 'brute_force' table (remote_addr): EA 1.2.3.4 EA 2.3.4.5 EA 3.4.5.6 EA 4.5.6.7 EA 5.6.7.8 EA 6.7.8.9 EA Contents of the 'block_ip' table (remote_addr): EA 2.3.4.5 EA 4.5.6.7 EA Can someone help me with the query that will select all the EA 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' EA table? EA Something like: EA select brute_force.* from brute_force, block_ip where EA brute_force.remote_addr != block_ip.remote_addr EA maybe? I have a feeling it's some sort of left join, and I was never EA very good at those. :-/ Yes, you need LEFT JOIN :) SELECT brute_force.* FROM brute_force LEFT JOIN block_ip ON brute_force.remote_addr=block_ip.remote_addr WHERE block_ip.remote_addr IS NULL; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Mysql SELECT question (LEFT JOIN?)
Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ - 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
Query question in join table with null
I have been battling with a complex query that us giving me a tremendous headache.. I have a MASTER table with 7 other tables (I can not change the database) The master table contains values (Key's) which I then need to lookup in another table that has 2 fields, the Key and a Description field. Everything works fine, but when one field in the master table has a null value, the entire query craps out. In MSSQL, I would use a IF THEN ELSE statement to get around this.. Is there such a feature in MySQL (running v3.23)? My Query is below: Select MASTER.N_NUMBER, MASTER.SERIAL_NUMBER, MASTER.YEAR_MFR, MASTER.REGISTRANT_NAME, MASTER.STREET1, MASTER.STREET2, MASTER.REGISTRANT_CITY, MASTER.REGISTRANT_STATE, MASTER.REGISTRANT_ZIP_CODE, MASTER.REGISTRANT_REGION, MASTER.COUNTY_MAIL, MASTER.COUNTRY, MASTER.LAST_ACTION_DATE, MASTER.CERTIFICATE_ISSUE_DATE, MASTER.APPROVED_OPERATION_CODES, MASTER.TYPE_ENGINE, MASTER.MODE_S_CODE, MASTER.FRACT_OWNER, ACFTREF.MANUFACTURER_NAME, ACFTREF.MODEL_NAME, ACFTREF.AIRCRAFT_TYPE, ACFTREF.ENGINE_TYPE, ACFTREF.AIRCRAFT_CATEGORY, ACFTREF.AMATEUR_CERTIFICATION, ACFTREF.NUMBER_ENGINES, ACFTREF.NUMBER_SEATS, ACFTREF.AIRCRAFT_WEIGHT, ACFTREF.AIRCRAFT_CRUISING_SPEED, AIRCRAFT_CODES.DESCRIPTION, AIRWORTHINESS_CODES.DESCRIPTION, ENGINE.ENGINE_MANUFACTURER, ENGINE.ENGINE_MODEL_NAME, ENGINE.ENGINE_TYPE, ENGINE.ENGINE_HORSEPOWER_THRUST, ENGINE.FUEL_CONSUMED, REGISTRANT_CODES.DESCRIPTION, MASTER_STATUS_CODES.DESCRIPTION From MASTER MASTER, ACFTREF ACFTREF, AIRCRAFT_CODES AIRCRAFT_CODES, AIRWORTHINESS_CODES AIRWORTHINESS_CODES, ENGINE ENGINE, REGISTRANT_CODES REGISTRANT_CODES, MASTER_STATUS_CODES MASTER_STATUS_CODES Where ( MASTER.AIRCRAFT_MFR_MODEL = ACFTREF.MANUFACTURER_MODEL_SERIES AndMASTER.TYPE_AIRCRAFT = AIRCRAFT_CODES.AIRCRAFT_CODE AndMASTER.AIRWORTHINESS_CLASSIFICATION = AIRWORTHINESS_CODES.AIRWORTHINESS_CODE AndMASTER.ENGINE_MFR_MODEL = ENGINE.ENGINE_CODE AndMASTER.TYPE_REGISTRANT = REGISTRANT_CODES.REGISTRANT_CODE AndMASTER.STATUS = MASTER_STATUS_CODES.STATUS_CODE ) AND MASTER.N_NUMBER = '3868J' The MASTER.STATUS field is the one that could sometimes contain a NULL.. Any help would be greatly appreciated! Dennis - 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
SQL question regarding JOIN
I have two tables, one contains user account info the other contains active sessions in a web app. In the user account table I have a column that contains each user's 'access level'. I'm somewhat new to relational databases and SQL, and though the following is working for me, I want to find out if it is the best way to handle it. Obviously, during the user's session it is often necessary to determine their specific access level. Right now I am doing it in this fashion... The session table looks like this: session_id int(10) primary key, username varchar(16) 'this is the foreign key related the user table' created datetime, updated datetime The user table looks like this: username varchar(16) primary key, password varchar(16), firstname varchar(30), lastname varchar(30), email varchar(100), access int(10) So when I need to determine the access level of a user during a given session I use the following query: select user.access from user, session where session.session_id = '$id' and session.username = user.username; This is working fine right now... but exactly how it is working and/or what it is actually doing still confuses me a little bit. The reason I did it this way is because at any given time I know the $id (session id) variable. It seemed more elegant to do it as a join rather than first grab the username from the session table with a select statement, and then use the username to grab the access level from the user table with another select statement. Is this a good looking query?? Or is there a better way to do this? Thanks! - Ben - 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
