JOIN
I have 5 tables all containing one field in common call it ARCHIVO, these field the data has a format very simple m9999001.001 with all of them I need to take out all data inner joinning with the FIELD ARCHIVO I can do that, but now I have a new table with the same field ARCHIVO but this time the format for ARCHIVO change!!! instead of the usual one that i describe a few lines before, this time the format came like this m9999001.err and to top it all up this turns out to be a very important data now so I need to get all the data from these table inner joinning with all the other ones
This perhaps is useless but I'll put it anyway :
I try a direct approch and did a SELECT well take a look :
SELECT * FROM proyecto.r501 INNER JOIN proyecto.firmas ON proyecto.r501.archivo = proyecto.firmas.archivo;
I not need to tell you that this effort of my did not work after 10 minutes running came up empty ( I already knew)
So the question is how can I make it work?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want. The tables are: table services service_id service_name table services_provided service_id service_date (date field) cust_id service_quantity I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time The query: SELECT service_date, service_name, service_quantity FROM services LEFT JOIN services_provided ON services_provided.service_id = services.service_id WHERE cust_id = $cust_id AND MONTH(service_date) = 10 AND YEAR(service_date) = 2007 GROUP BY service_id ORDER BY service_id (Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.) I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table. But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results. Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.
Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results: QUERY 1: SELECT COUNT( o.orderID ) FROM order o WHERE DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 161 QUERY 2: SELECT COUNT( o.orderID ) FROM order o LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID WHERE DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 175 QUERY 3: SELECT COUNT( o.orderID ) FROM order o, credit_card cc WHERE o.orderID = cc.orderID AND DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 157
Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables: Week (with columns day and hour) Activity (with columns day, hour, activityid and ac_text) Person (with columns name and activityid) I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement: Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour I can then make a loop (I am usin asp.net) that writes the activities. My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid). I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.
Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs! So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using: JOIN myTable ON xyx=abc but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference. However the only way I can force the join order is to use STRAIGHT_JOIN that does not accept an ON condition, so I have shifted the clauses to the WHERE and it works fine. Is there any syntax I can use to keep the ON conditions, I prefer this approach it makes the code clearer regarding intent. Code:
Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs! So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using: JOIN myTable ON xyx=abc but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference. However the only way I can force the join order is to use STRAIGHT_JOIN that does not accept an ON condition, so I have shifted the clauses to the WHERE and it works fine. Is there any syntax I can use to keep the ON conditions, I prefer this approach it makes the code clearer regarding intent. Code:
Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID. For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.
Using LEFT JOIN Instead Of A Equi-JOIN
I have a SQL statement in some code I'm trying to get my head around.. I havent used SQL that much so I assume this is a newbie question: Why would someone use LEFT JOIN if they can simply construct the statement with equi-JOIN? The first statement uses left joins and the 2nd is my reconstruction using equi-JOINs.. so far they produce the same results (however it could be I dont have the right kind of test data) So to summarize my questions: Why do it using LEFT JOINS which I personally find harder to read over the equi-JOIN, 2nd Do they acutally produce the same result everytime? 1st (LEFT JOIN) ------------------------------------------------------------- SELECT action.action, summary.gatekeepercl, branch.branch FROM summary LEFT JOIN action ON summary.action=action.id LEFT JOIN branch ON summary.branch=branch.id WHERE summary.gatekeepercl IN (506100,506101) 2nd (equi-JOIN) --------------------------------------------------------------- SELECT action.action, summary.gatekeepercl, branch.branch FROM summary, action, branch WHERE summary.action=action.id AND summary.branch=branch.id AND summary.gatekeepercl IN (506100,506101)
JOIN Syntax (INNER JOIN), Self Join
I want to find the most recent purchase for each customer(see below) and I tried: SELECT A.* FROM mytable A INNER JOIN ( SELECT full_name, Max(entered_when) AS entered_when FROM mytable GROUP BY full_name ) M ON A.full_name=M.full_name AND A.entered_when=M.entered_when ; I got a syntax error: 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 full_name, Max(entered_when) AS entered_when FROM mytable My data looks like: full_name product_purchased entered_when phil shoes 20050227121012 mary purse 20020212000000 phil socks 20021226101423 mary bra 20020111000000 (I just want to get phil's shoes and mary's purse.) What should I be doing?
Need Help With Join And Values In The Join
I set up a join using two tables (description and product). I have multiple products that use the same description. I also have different material types for the products. The SQL is "SELECT descripID, subCategory, ProdID, subCat, materialType FROM products, descriptions WHERE subCategory = subCat". from the description table: - descripID - subCategory from the product table: - ProdID - subCat - materialType What I want to do is get the descripID and materialType into a new table. There is going to be multiple descripIDs that match multiple materialTypes. I only want one materialType to be with a particular DescripID. for example: descripID: 01 materialType: 01 descripID: 01 materialType: 02 descripID: 02 materialType: 01 descripID: 03 materialType: 03 I hope this helps. Here is some of the output I currently have: DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00024 Prod Material: 01 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00025 Prod Material: 01 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00026 Prod Material: 03 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00027 Prod Material: 02 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00028 Prod Material: 02
To JOIN Or Not To JOIN... Or Am I Missing Something...?
Right, I was always under the impression that it was 'better' to use JOINs, partly because it is 'faster'. I'm now wondering if that is simply a myth. Take these two SQL statements: SELECT DISTINCT p.ProductID, p.Image, p.Price FROMproducts AS p RIGHT JOINcategory_links AS c_l ONc_l.ProductID= p.ProductID INNER JOINcategories AS c ONc.CategoryID= c_l.CategoryID RIGHT JOINbrands AS b ONp.BrandID= b.BrandID RIGHT JOINsize_links AS s_l ONs_l.ProductID= p.ProductID INNER JOINsizes AS s ONs.SizeID= s_l.SizeID RIGHT JOINcolour_links AS co_l ONco_l.ProductID= p.ProductID INNER JOINcolours AS co ONco.ColourID= co_l.ColourID SELECT DISTINCT p.ProductID, p.Image, p.Price FROMproducts AS p, category_links AS c_l, categories AS c, brands AS b, size_links AS s_l, sizes AS s, colour_links AS co_l, colours AS co WHEREc_l.ProductID= p.ProductID ANDc.CategoryID= c_l.CategoryID ANDp.BrandID= b.BrandID ANDs_l.ProductID= p.ProductID ANDs.SizeID= s_l.SizeID ANDco_l.ProductID= p.ProductID ANDco.ColourID= co_l.ColourID The first one uses JOINs and the second simply uses WHERE. As a matter of information, both have additional WHERE details added to refine the search. I'm using a fast PC, and there are only 14 products in the database (and not all that much data in the other tables). However, I was getting REALLY slow script execution, and I traced it to the SQL query. Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact. This kinda tells me NOT to use JOINs... and to stick with WHERE for this. But in that case, I am left confused as to where it is appropriate to use JOINs and where it isn't...? I did a couple of hours of Googling and didn't clear the matter up. All the articles I found pointed towards using JOINs. Obviously at the end of the day I'm going to use the faster method. Plus, after thinking about it, there's a lot more work being done with the JOINs, is there not...? I'd love to know what some other people think about this, and whether I'm just totally out on my JOIN usage or if other people are using them in the same situations. ::] krycek [::
Inner Join Or Left Join?
What I am trying to do is this.... English Table: Number Text Roman 1 One I 2 Two II 3 Three III 4 Four IV Hindi Table: Number Text 2 Do 3 Teen 4 Char 5 Panch Expected Results where number is 2 Text Roman Two II Do II
JOIN Within LEFT JOIN
I am using MySQL 3.23.54. I have the following table structure. FORMS form_id (PK) form_name STAFF ASSIGNMENTS staff_assignment_id (PK) form_id (FK) staff_id (FK) STAFF staff_id (PK) first_name last_name For each record in FORMS there may be zero, one or multiple records in STAFF ASSIGNMENTS. I need to perform a left join from FORMS on STAFF_ASSIGNMENTS. When there is a record in STAFF ASSIGNMENTS, I need to perform a join with STAFF to retreive staff name. Here is my attempt at the query. SELECT forms.form_id, forms.form_name, staff.first_name, staff.last_name FROM forms LEFT JOIN staff_assignments ON forms.form_id = staff_assignments.form_id (JOIN staff on staff_assignements.staff_id = staff.staff_id) How do I need to write the query?
LEFT SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself? I want to see all records in a table where the year of the sales date is 2003 and where the salesman sold an item to a customer to which he did not sell that item to in 2002. The following is an example table (SALES): +----+-----+------+------------+--------+------+-----+ | ID | SLM | CUST | DATE | ITEM | COST | QTY | +----+-----+------+------------+--------+------+-----+ | 1 | 1 | AAA | 2002-01-01 | APPLE | 1.00 | 2 | | 2 | 1 | BBB | 2002-01-01 | APPLE | 1.00 | 2 | | 3 | 1 | CCC | 2002-01-01 | PEAR | 1.00 | 2 | | 4 | 1 | AAA | 2002-01-01 | PEAR | 1.00 | 2 | | 5 | 2 | AAA | 2002-01-01 | APPLE | 1.00 | 2 | | 6 | 3 | CCC | 2002-01-01 | BANANA | 1.00 | 2 | | 7 | 1 | AAA | 2003-01-01 | APPLE | 1.00 | 2 | | 8 | 2 | AAA | 2003-01-01 | APPLE | 1.00 | 2 | | 9 | 3 | CCC | 2003-01-01 | BANANA | 1.00 | 2 | | 10 | 1 | AAA | 2003-01-01 | BANANA | 1.00 | 2 | | 11 | 3 | CCC | 2003-01-01 | APPLE | 1.00 | 2 | +----+-----+------+------------+--------+------+-----+ What I want to see in my result is that in 2003 Salesman 1 sold bananas to customer AAA and salesman 3 sold apples to customer CCC. I want to see these because these salesmen did not sell these customers these items in 2002.
LEFT SELF Join -- LEFT Join On Same Table
Is it possible to LEFT JOIN a table to itself? I want to see all records in a table where the year of the sales date is 2003 and where the salesman sold an item to a customer to which he did not sell that item to in 2002. Code:
(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?! I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better? I know that joining with join keyword is better for explicit code but what about performance?
Do I Need A Join?
I have a PHP address book that stores birthday information (in addition to standard stuff like name, address, etc.) in a MySQL database. The address book works fine, but I want to add the ability to send email reminders. BUT, and this is what makes things more interesting, I also want to be able to do reminders that are not related to any particular contact. As an FYI, the database currently has two tables: <users> and <contacts>. I'll try to briefly explain, please bear with me. I want to add a <reminders> table to the database so users can specify an event date and have an email sent to them prior to said date. The reminders can be independent of their <contacts> data (e.g. a user wants a reminder of an upcoming massage therapy appointment) OR the reminder could be for their friend John Doe's birthday (which IS stored in the <contacts> table). How can I setup a <reminders> table that can stand on its own but also reference birthday info in the <contacts> table? Feel free to point me towards any appropriate tutorials, howto's, etc.
More JOIN Fun (Or Is It?)
Yes, I've read about JOINs, albeit after coding for a couple of years already using queries like the following: "SELECT m.LastName, m.FirstName, o.Address FROM members m, offices o WHERE o.City='$vCity' AND m.Status<>'Retired' AND m.Status<>'Suspended' AND o.MemberID=m.MemberID ORDER BY LastName,FirstName" ($vCity comes from a drop-down list of city names) Now... everything works just fine, and has for a long time.... but reading this NG, and some online articles about JOINs has be wondering/perplexed... What I am hoping for is someone who knows MySQL and is really bored to perhaps explain why the above query is NOT a 'real' join (which i don't think it is), and why that's necessarily BAD. How can I take that query and turn it into a 'real' join, and more importantly, why should I? What is 'wrong' with queries like the one above?
JOIN Using NOT IN
You may recongize this as I posted a similiar problem. Please let me know if there needs to be clarification on this. Here is the example: You've got Dan who started Group A and in Group A is Dan, Rudy and Longneck. Rudy Starts Group B and has invited Kristen and Dave. Now Rudy is their "ref" When Running this Query I would want a list of pople that the Members of Group A have invited but are not in Group A. So the query would result in Kristen and Dave coming up. Now lets say that Dave joins Group A. I no longer want him to show up in the results. Here is the Table Breakdown PHP -- -- Table structure for table `GroupUsers` -- CREATE TABLE `GroupUsers` ( `id` int(11) unsigned NOT NULL auto_increment, `GroupID` int(11) unsigned NOT NULL default Ɔ', `UserID` int(11) unsigned NOT NULL default Ɔ', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; -- -- Dumping data for table `GroupUsers` -- INSERT INTO `GroupUsers` VALUES (1, 1, 1); INSERT INTO `GroupUsers` VALUES (2, 1, 2); INSERT INTO `GroupUsers` VALUES (3, 1, 3); INSERT INTO `GroupUsers` VALUES (4, 1, 4); INSERT INTO `GroupUsers` VALUES (5, 2, 3); INSERT INTO `GroupUsers` VALUES (6, 2, 4); INSERT INTO `GroupUsers` VALUES (7, 2, 5); -- -------------------------------------------------------- -- -- Table structure for table `Groups` -- CREATE TABLE `Groups` ( `GroupID` int(11) unsigned NOT NULL auto_increment, `GroupName` varchar(75) NOT NULL default '', PRIMARY KEY (`GroupID`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; -- -- Dumping data for table `Groups` -- INSERT INTO `Groups` VALUES (1, 'Group A'); INSERT INTO `Groups` VALUES (2, 'Group B'); -- -------------------------------------------------------- -- -- Table structure for table `Users` -- CREATE TABLE `Users` ( `UserID` int(11) unsigned NOT NULL auto_increment, `firstname` varchar(25) NOT NULL default '', `ref` int(11) unsigned default NULL, PRIMARY KEY (`UserID`) ) TYPE=MyISAM AUTO_INCREMENT=6 ; -- -- Dumping data for table `Users` -- INSERT INTO `Users` VALUES (1, 'Dan', NULL); INSERT INTO `Users` VALUES (2, 'Longneck', 1); INSERT INTO `Users` VALUES (3, 'Rudy', 1); INSERT INTO `Users` VALUES (4, 'Kristen', 3); INSERT INTO `Users` VALUES (5, 'Dave', 3); Currently PHP SELECT DISTINCT U.userid FROM Users U INNER JOIN GroupUsers GU ON GU.UserID = U.UserID INNER JOIN GroupUsers GU2 ON GU2.UserID = U.ref AND GU2.GroupID =1 Brings back Everyone Except Dan when I would like it to Only return Users referred by Group A Users but are not in Group A. This would result in the Query Bringing back Dave only. Please let me know if you need more clarifaction
Self Join?
I have the following table CREATE TABLE `users` ( `userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR( 16 ) NOT NULL , `approved` TINYINT NOT NULL ) TYPE = MYISAM ; approved always switches 0/1 My simple Question is: How can I get the following to work? SELECT COUNT(u1.userid) as user_approved, COUNT(u2.userid) as user_not_approved FROM users u1, users u2 WHERE u1.approved = 1 AND u2.approved = 0
Help With A Join
i have two tables, events and schedules i need to output all the events with a current schedule, but some events have multiple schedules and therefore im getting duplicate rows in my results im using this query currently: SELECT * FROM events INNER JOIN schedules ON schedules.event_id = events.event_id AND schedules.recursuntil >= NOW() ORDER BY event_title ASC can anyone help with the right way to do this so that i only get one record from events even if there are three matches in schedules?
Join Where
This query is suppoused to give the debt value "venta" nro 7. It works perfect, except for the last line, the "WHERE ventas.id = 7" SELECT ventas.id, ventas.importe_acordado - SUM(pagos.importe) as valor_dinamico FROM ventas LEFT OUTER JOIN pagos ON pagos.venta = ventas.id GROUP BY ventas.id WHERE ventas.id = 7
Join!
How would I make a JOIN work here? The whole something.somethingeelse is confusing to me. PHP <?php $result8 = mysql_query("SELECT * FROM playlist ORDER BY id DESC LIMIT 5") or die(mysql_error()); while($row8 = mysql_fetch_array( $result8 )) { $game=$row8['game']; $system=$row8['platform']; $result4 = mysql_query("SELECT * FROM games WHERE boximage!='' AND game NOT LIKE '!%' AND game = '$game' AND system='$system' LIMIT 5") or die(mysql_error()); while($row4 = mysql_fetch_array( $result4 )) { $image=$row4['boximage']; $game=$row4['game']; $console=$row4['system']; echo " <td width=颼' align='center' valign='top'> <a href='http://www.gameanyone.com?p=play&game=$game&cc=$console'> <img src='images/$image' border=Ɔ' height=飮' width=飆'></a><br> <a href='http://www.gameanyone.com?p=play&game=$game&cc=$console'>$game</a></td>"; }} ?>
SQL Join
I've been looking at this a while, and now my minds just gone blank. I have three tables, two of which Join correctly, however I cannot seem to join HC_PIM_WebParts_Attributes_Lookup with the HC_PIM_WebParts_Attributes.id column = HC_PIM_WebParts_Attributes_Lookup.AttributeID as this always returns null. mysql> DESCRIBE HC_PIM_WebParts; +-------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+----------------+ | WebPartID | int(11) unsigned | NO | PRI | | auto_increment | | Name | varchar(50) | YES | | || | Description | varchar(255) | YES | | | | | AttributeLookupID | int(11) unsigned | YES | | || | WebPartTemplate | text | YES | | || | DataTables| varchar(100) | YES | | || | URL | varchar(255) | NO | | || +-------------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> DESCRIBE HC_PIM_WebParts_Attributes; +-------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+----------------+ | id| int(11) unsigned | NO | PRI | | auto_increment | | AttributeLookupID | int(10) unsigned | NO | MUL | 0 || | AttributeKey | varchar(255) | NO | | || | AttributeValue| varchar(255) | YES | | || | Description | varchar(255) | YES | | || | DataTypeID| int(11) | NO | | || | Internal | enum(Ƈ',Ɔ')| YES | | 0 | | +-------------------+------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> DESCRIBE HC_PIM_WebParts_Attributes_Lookup;............
Help With Join
I'm trying to pull data from 2 tables, all the data from table playerinfo and the row rank from table ranks matching them by name in both tables PHP $pi = mysql_query("SELECT * FROM playerinfo where name.ranks = name.playerinfo"); PHP CREATE TABLE `ranks` ( `rank` int(11) NOT NULL default Ɔ', `name` varchar(255) NOT NULL default '', `kills` int(11) NOT NULL default Ɔ', `deaths` int(11) NOT NULL default Ɔ', `score` int(11) NOT NULL default Ɔ', `mvp` int(11) NOT NULL default Ɔ', `games` int(11) NOT NULL default Ɔ', `gdi` int(11) NOT NULL default Ɔ', `nod` int(11) NOT NULL default Ɔ', PRIMARY KEY (`name`) ) TYPE=MyISAM; PHP CREATE TABLE `playerinfo` ( `score` int(5) NOT NULL default Ɔ', `name` varchar(80) NOT NULL default Ɔ', `team` varchar(80) NOT NULL default Ɔ', `kills` varchar(80) NOT NULL default Ɔ', `deaths` varchar(80) NOT NULL default Ɔ', `ping` varchar(80) NOT NULL default Ɔ', `timeplayed` varchar(80) NOT NULL default Ɔ' ) TYPE=MyISAM;
Odd Join
I am developing a website and it runs perfectly on the test server. But as soon as I moved it to the developer server I started getting an odd error. I have included the full query though it does not seem to pertain to the error. The error I am recieving is. #1064 - You have an error in your SQL syntax near 'ON news.newsId=authorNews.newsId LIMIT 0, 30' at line 1 gotta love that 1064 error. lol. I have run the query and it works perfectly on the test system. I thought maybe the db tables were set up wrong but the column names etc are exactly the same. But for some reason the server doesn't like the "ON " part of the join. //This is the section pertaining to the error SELECT DISTINCT news.newsId, news.headline, news.posted FROM news JOIN authorNews ON news.newsId = authorNews.newsId //This section works perfectly on the server but when the ON is added it fails to recognize SELECT DISTINCT news.newsId, news.headline, news.posted FROM news JOIN authorNews //This is the original query SELECT DISTINCT news.newsId, news.headline, news.posted FROM news JOIN authorNews ON news.newsId=authorNews.newsId JOIN authors ON authorNews.authorId=authors.authorId JOIN paperNews ON news.newsId=paperNews.newsId JOIN paper ON paperNews.paperId=paper.paperId JOIN sectionNews ON news.newsId=sectionNews.newsId JOIN sections ORDER BY news.posted DESC LIMIT 25, 25
JOIN
SELECT * From Schedule; +---------+--------+-------+------------+-----------+ | ScheduleNo| custNo | EmpNo | Date | Time | +---------+--------+-------+------------+-----------+ | 2 | 2 | 3 | 2006-01-01 | 1:00:00 | I want to be able to select ScheduleNo 2, the customers details such as firstname and lastname based on the custno and the empno based on the empno. I also need to select where the date is 2006-01-01. I just want a simple join that works with three 3 or more tables...
Join
I am new to myssql and am using a mysql database with the following tables Table Itinerary itinerary_id int(4) ship_id int(4) itinerary_title varchar(10) itinerary_duration varchar(10) date_from date date_to date from_port_id int(4) to_port_id int(4) port_destinations text details text Table Country country_id int(4) region_id int(4) country_name varchar(30) Table Region region_id int(4) region_name varchar(30) Table Port port_id int(4) country_id int(4) port_name varchar(30) port_desc text image_path varchar(50) in my html form, I choose a field which fetches the data from the region table. From the region table I select the region_id and then find the corresponding record(country_id) in the country table. I now use the country_id to find the port_id from the port table. Once I have this port_id, I can perform the desired query and fetch the data from the itinerary table. I am able to do this using three queries but that takes a lot of time.
Inner Join
I am new to this type of query and while I've found a lot of help on the forums, I am simply not getting it to work. Here is my attempt currently:
Join?
Two tables. First table has columns ID and TEXT. Second table has columns ID1 and ID2. I would like to select rows from the second table where ID1 and ID2 are both found in the set of IDs returned by a select on the first table. To be more specific, I'm selecting IDs from the first table using a full text search on the TEXT column. Then I need to identify rows in the second table where ID1 and ID2 are both found in the IDs from the select on the first table.
Join Possible?
Suppose that I have 2 tables. A table "A" with fields "Brand" and "Spec" and the other "B" with fields "ID_Specs" and "Type". Example: Table A Brand - Spec 1 - "yo" 2 - "hello" 3 - 1 3 - 2 6 - "bye Table B ID_SPEC - Type 1 - "kiss" 2 - "kiss1" 3 - "kiss2" If the field "Brand" of the table A is "3", the field "Spec" is an "ID_Spec" link for the table B. To save a lot of work, I would like to know if I can make some kind of Join or Temporary table with all the normal fields of the table "A" and the linking for the table B Example Table C 1 - "yo" 2 - "hello" 3 - "kiss" 3 - "Kiss1" 6 - "bye"
Inner Join
Here is what I need to do- I have 2 tables related by an ID (int) field. I need to return all fields from one table and none from the second but I need to run a comparison against a field on the second table and if the field data equals 'x' then the corresponding record from the first table should not be returned. Seems simple to just say it, lol. I changed the way I was querying the db in the middle of the development so this particular situation wasn't anticipated when I created the db. Code:
Using JOIN
I was under the impression that the following was going to return all records that exist between the 2 dates mentioned below showing NULL values for the "billing_commnets.comment" column when it is blank. However this is only returing records that have values in both tables that match. Any ideas on what I am doing wrong? SELECT customer_ads.job_title, customer_ads.start_date, customer_ads.client_id, billing_commnets.comment FROM customer_ads JOIN billing_commnets ON (customer_ads.id = billing_commnets.orderID) WHERE (customer_ads.start_date between '2007-06-02' AND '2007-06-12')
Inner Join
I'm trying to select the every thing from table 2 that is realted to table 1s id. but i only have the username variable. I hope this makes sence. here is my query: $user_profile = $_SESSION['username']; $sql="SELECT * FROM general_profile INNER JOIN user_info WHERE user_info.username = '$user_profile' and general_profile.profile_id = user_info.id"; If i do the WHERE section with only one (either one) of the commands it works, but when i do both it dont.
Join
I have a table which has columns seq and price. Some sample rows are... seq price 1 34 1 35 2 25 2 29 2 31 3 31 4 30 4 31 I would like to return the single row which the largest seq number and smallest price within that seq group. So for the example above the second to last row would be returned (seq=4, price=30). I am running MySQL 4.0.20 so unfortunately I cannot do subqueries. Would someone be able to suggest how this could be done with joins?
JOIN
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDate othertableid value -------- ------------- ------------ ----- 1 2004-01-10 1 10 2 2004-01-20 1 20 3 2004-01-20 2 70 4 2004-01-10 2 80 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
INNER JOIN LIKE?
I've come across a problem that I haven't faced before and was wondering if it can be done. Basicly the SQL goes like this: SELECT.... FROM [tablea] INNER JOIN [tableb] ON [tablea].[column] LIKE [tableb].[column] [tablea].[column] = "example" [tableb].[column] = "example, example2" So I want to do a normal table join, except I want it to pick up the records that match but arn't EXACT. So [tableb].[column] should be picked up if [tablea].[column] had "example" Can be done???
Using Sum() In Join
I have two tables one that keeps track of billing and one that keeps track of payments. What I am trying to do is come up with a select statement that picks the sum of all the billing records and payment records for all customers. I am using the following statement: select tblBilling.CustomerID, Sum(tblBilling.ServiceCost), Sum(tblPayments.PaymentAmount) from tblBilling left join tblPayments using (CustomerID) group by tblBilling.CustomerID The statement runs but returns inaccuarate information. For example if the customer has multiple payment records the statement multiplies the sum by the amount of payments records (ie. a customer should have a total of 798 but I get 2394). If a customer has only one record in each table I get the correct answer. Anyone have any ideas?
SQL JOIN
I have two tables, (let's call them Table A and Table B). Table B has a foreign key that relates it to table A, (ie JOIN Table B ON Table_B.fk = Table_A.key). Table B also has a date field. What I want to do is make a join where we return only the record in table B with the highest, (most recent or farthest into the future), date value for each record in Table A. I tried to use WHERE MAX(Table_B.date_col) but hadn't realised that I can't because I need to use GROUP BY, which I havent' been able to make work. Does anyone know what I need to do?
Join If ?
I've been scratching so hard, that I'm getting splinters..... try to simplify some horrific legacy code with embedded layers of the SQL queries. 3 tables - Customers - Salespersons - Sales If only one salesperson has been involved in sales to a customer, list the salespersons details beside the customer details - otherwise display just the customer details.... all customers are to be listed.
JOIN
Lets say i have 3 tables. INVENTORY TABLE: | invt_id | color_id | shape_id | | 0 | 3 | 2 | | 1 | 2 | 1 | COLOR TABLE: | color_id | name | | 2 | Red | | 3 | Black | SHAPE TABLE: | shape_id | name | | 1 | pear | | 2 | round | whats the most efficient way to join the names from the color and shape tables into the inventory table based on the ids?....i have in reality like 10-15 reference tables to join and would like to keep the SQL as short as possible.
Inner Join Or More?
I'm starting creating for me more interessting queries but I stuck at the following part, i have a table like this: t_messages (table): m_id m_fk_u_id_s* m_fk_u_id_r* m_title m_text m_datum m_read * those are to foreign keys to the t_user (table) where of course is: t_user (table): u_id u_username ... ... Now, I want do a Query which shous me: m_title, m_text, m_fk_u_id_s but of course not the id, the username, same for the other fk key: MsgTitel, MsgText, UserSender, UserReceipt
SQL Join
I have four tables - person, movie, rating and rel_movie_rating. The important columns in these tables are person.id, movie.id and movie.title, rating.value and rating.person_id, rel_movie_rating.movie_id and rel_movie_rating.person.id. Now what I want to do is to find all the movies a certain person has rated and their corresponding rating values. I've tried for a couple of hours now but I always end up querying for all the ratings in the database (over half a million), i'm guessing that I need to do some sort of inner join here but can't quite figure out how. Any ideas on how one should go about to solve this problem?
JOIN
lets say i have 2 tables: FILES: file_id | filename | 3 | test2.jpg | 10 | test1.jpg | 14 | test3.jpg | ACCESS: id | user_id | file_id | 0 | 1 | 3 | 1 | 1 | 14 | 2 | 8 | 10 | 3 | 8 | 14 | This gets all the filenames a user 1 has access to: SELECT * FROM access INNER JOIN files ON (access.file_id=files.files_id) WHERE user_id=1 But i dont know how to get all the filenames that a user DOESN'T have access to.
JOIN
I've got two tables (a bit cut here): albums (aid, visibility) pictures (pid, aid, name, title) // pid increases by 1 for each new row I'd like to select the latest addition from pictures, but if aid for that picture in table albums has a visibility set to 1 next row should be checked. Unclear enough...? SAMPLE: albums 1, 0 2, 1 pictures 11, 2, pic1.gif, Pic1 10, 1, pic2.gif, Pic2 In this little sample it would jump Pic1 with highest pid (11) because albums.aid visibility is 1. Instead it would chose Pic2 Maybe a bit clearer So is it possible to do in one go, or would I need to make two querys?
Inner Join
I am trying to do a INNER JOIN of 2 tables. but I am not getting any results or any errors. I hope you can help as I have been trying this for a few hours now without any success. Here is the SQL: SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.teamneame = table2.teamname Obviouslt both tables have data and the teamname will match up. Like I have said it is not returning any results. If I do a LEFT , RIGHT join I only get data from that side.
Join From 2 Dbs
I'm trying to run a query that uses columns from two tables, but the tables belong to two different databases. The structure looks something like this. DB: ki_ideas Table: ideas Fields: ideaid, user DB: ki_users Table: users Fields: username, firstname, lastname Note that idea.user = users.username. This is they query that I have so far: SELECT user, count(ideaid), FROM ideas GROUP BY user ORDER BY count(ideaid) DESC I need to run a query that selects all listed fields. So, more specifically, I need to add users.firstname and users.lastname where ideas.user = users.username to this query.
Cannot Use WHERE Because Of JOIN
an invoice table that displays the customers name from the job the invoice is relate to SELECT I.invoiceno, I.jobref, J.custname, I.invoicetotal, DATE_FORMAT(I.invdate,'%d/%m/%Y') FROM invoices AS I JOIN jobs AS J ON I.jobref = J.jobref the challenge - i need to be able to display ONLY unpaid invoices, ie. I.invpaid='No', but because of the join I get a syntax error when I add a WHERE clause.
Is JOIN With SUM() Possible?
I am having difficulty coming up with a valid syntax, and I don't even know if it is possible. I have two tables: orders and order_item orders has information like customer_id, date_purchased and so on. order_item has: id (key) orders_id product_id quantity unit_price total_price What I need to do is to get all (*) fields from orders, and (SUM(order_item.quantity) WHERE orders.id = order_item.orders_id) all in a single sql command. Basically the syntax I am currently trying to get to work is: SELECT a.*, SUM(b.quantity) FROM orders AS a, order_item AS b WHERE a.company_id = 3 AND a.id = b.orders_id I have a feeling that I need to do a LEFT JOIN as a nested statement... Any ideas?
Join
TABLE USERS; |Id|CardNr|Name| ---------------- |1 | 3 | Joe| ---------------- |2 | 6 | Jim| ---------------- |3 | 9 | Wow| ---------------- TABLE USERMEMBER |Id|CardNr|Name| ---------------- |1 | 3 |Owel| ---------------- |2 | 3 |Jany| ---------------- |3 | 3 |Jen | ---------------- |4 | 9 |Wipi| ---------------- |5 | 6 |WuWu| ---------------- |6 | 6 |HiPi| ---------------- Now i want to join them together with CardNr. I want to get like this. USERS Joe has 3 SubMembers Jim has 2 SubMembers Wow has 1 SubMember
|
TRACKER
