Delete From 2 Tables (join)
How are we all doing this fine day
small problem "man do i hate joins"
I have two tables "categories" and "links"
Links carry the cid field from categories.
PHP Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Is It Possible To Delete Using A Join?
I have two tables. Table_A and Table_B. Both tables have a list of usernames. Table_A's list is larger than Table_B's list. Is there a way of comparing the two tables to show which are the extra usernames in Table_A and deleteing them from that table? Is it possible to delete using a join?
View Replies !
Join 5 Tables
i need to join together 5 tables in a psql database i have made. the problem is that i cant seem to find the correct sql to perform the join. the tables i have are group, person, contacts, movements and treatments. group and person are connected by a group_id, established in group, each entry in the person table also has a group_id. the other tables are connected by person_number, established in person and a foreign key in all the other tables.
View Replies !
Join 3 Tables
Could someone tell me what's wrong with this query? I'm trying to join 3 tables and I've looked into everything to no avail. $query = "SELECT products_to_categories.*, products_description.*, products.* FROM products_to_categories LEFT JOIN products_description LEFT JOIN products ON products_to_categories.products_id = products_description.products_id = products.products_id" . "WHERE categories_id = '$categories_id'"; My error message refers to a syntax error near 'categories_id = Ƈ'' at line 1.
View Replies !
Update JOin Two Tables
I gotta the Two tables.. Table1 = inside -------------------- id email table2 = mailfile --------------------- id code from to inorout I want to compare the emails of inside with emails of mailfile and then if there is match I want to update "inorout" with a * Now what I did is run a query on inside checking for emails and while it does that I put another query in that while loop to match it , but I get timeouts... Is there anyother way, I was thinking the LEFT JOIN query but not sure how to do that.
View Replies !
Join Two Tables In MySQL
I'm trying to join two tables in mySQL and both tables have a commonly named column. It works just as I want in PHPmyAdmin but when I use it from a php script I get 'undefined' for widget.widget_id. SELECT widget_name, widget_description, widget_category, widget.widget_id FROM user_widgets LEFT JOIN widget ON widget.widget_id WHERE user_id =$user_id AND user_widgets.widget_id = widget.widget_id I'm new to DB's and this is my first ever join query. What am I doing wrong?
View Replies !
Join Tables In Two Databases
i have come across a time consuming problem. we have moved some of our tables in the database into a new one (but on the same server). In my php pages i used a lot the JOIN syntax in my queries. now some tables are moved to the new database. So the "join" queries stoped working. Is there a way to use the JOIN between the two databases or I must re-write the code?
View Replies !
Howto JOIN Tables
I'm looking for a solution on howto join multiple tables across databases. This bit of code does not work. yet. $SqlSelectQuery = ( " SELECT a.ID, a.Name, b.ID, b.Name FROM db1.a, db2.b WHERE a.ID = b.ID " ); $SqlSelectResult = mysql_query($SqlSelectQuery, $db1); $SqlSelectResult2 = mysql_query($SqlSelectQuery, $db2);
View Replies !
What Join To Use For Joining Three Tables In Mysql?
I have person Table which holds address key that points to "link-table" record which holds person ID and address table record ID. I want to get precise person and precise address so I thing that is disqualifying the LEFT and RIGHT joins. Remains the INNER join but I did not see it anywhere with more then two tables. My creation: "SELECT t1.name, t2.* FROM persons AS t1, addresses AS t2, persons_addresses as t3 WHERE t3.id = t1.address AND t2.id = t3.address" does not work the way I want.(IT is basically left join [or not?]) Please, can any mysql-php GURU help me? person id = persons_addresses.person person ... field address persons_addresses id = person.address persons_addresses ... field address addresses id = persons_addresses.address Table address holds all relevant info about the address like street, zip, city key, state and country. I can imagine the use of subselect if mySQL had one, but I can not actually imagine without it [and probably is only very simple matter].
View Replies !
LEFT JOIN On Very Large Tables
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the service. Here's the SQL... SELECT films.*, COUNT(reviews.id) FROM films LEFT JOIN reviews ON films.id = reviews.filmID GROUP BY films.id LIMIT 0, 100; JOIN works but doesn't return records for films with no reviews.
View Replies !
Join Two Tables And Paginate The The Results.
I'm looking for an easy to understand, easy to implement pagination script for PHP. I've used a script by Evolt before but have run into some problems. My main challenge this time is that it needs to join two tables and paginate the the results.
View Replies !
Left Join Mutliple Tables
I have this query: Code: SELECT articles . *, article_tags.art_id, COUNT( article_tags.id ) AS appres, COUNT( article_comments.art_id ) AS comms FROM articles LEFT JOIN ( article_tags, article_comments ) ON ( article_tags.art_id = articles.id AND article_comments.art_id = articles.id AND article_tags.art_id = article_comments.art_id ) GROUP BY id The simplified table structure is like Code: articles article_comments article_tags --------- ---------------------- -------------- id id id art_id art_id If I left join just articles to comments or just articles to tags, it shows the correct count number. When I join both it completely messes up the number in no logical way to me.
View Replies !
JOIN 2 Tables But Only Pull Certain Fields
PHP Code: include("dbinf.inc.php"); mysql_connect($sqladdress,$username,$password) or die(mysql_error()); @mysql_select_db($database) or die(mysql_error()); $query="     SELECT los_id, fname, lname, cel     FROM los     JOIN branches USING (branches_id)     WHERE branches_id='".$_GET['id']."' "; $result=mysql_query($query) or die(mysql_error()); $row = mysql_fetch_array($result); mysql_close(); Basically, I only want to pull the branches_id and phone number field from the branches table.With the code above I am going to be listing Loan Officers for a specific branch location ($id) and if they dont have a cell phone # I am going to be outputting the phone # of the branch. Obviously I am getting all of the branches table when I only want 2 fields .
View Replies !
Link 2 Tables, But JOIN 2 Times.
I have 2 tables Table:tag_tag_link tag_id1|tag_id2 Table:tag tag_id|tag_name Now, I want to join these 2 tables, but with 2 join statement because each tag_id in tag_tag_link refer to the tag_id in tag... what can I do, like this? SELECT * FROM tag_tag_link LEFT JOIN tag ON tag1_id = tag_id LEFT JOIN tag ON tag2_id = tag_id surely that doe not seems right... any ideas?
View Replies !
Mix Two Tables In A Single SELECT Statement - Not A Join
I have an Intranet with the organisation I work with. In this organisation there are two departments - community department and press office. Both the community department and press office have thier own news system where they can add news in their own admin section that will appear on their homepages. Both admin section have their own DB table thus two seperate tables. I would like to select all the rows from both tables in a single SELECT statement so when I output them they will be integrated. This is not the same as a table join as their are no keys and I want the rows from both to remain seperate. Can this be done? Could someone please post an example SQL statement demonstrating this if so?
View Replies !
Multiple Sorts On A Join Statement Using 3 Tables
I have the foll. $sql which works well. $sqla = "SELECT * FROM Customers c INNER JOIN Custaddress a USING(CID) INNER JOIN Cities t USING(CITYID) WHERE c.Company LIKE '%$customer%' ORDER BY Company asc"; But if I wish to do a sort on 3 fields in 3 different tables, i'm getting an error. $sqla = "SELECT * FROM Customers c INNER JOIN Custaddress a USING(CID) INNER JOIN Cities t USING(CITYID) WHERE c.Company LIKE '%$customer%' ORDER BY c. Company asc, t.City asc , a.Zip asc ";
View Replies !
2 Tables Left Join Or Seperate Queries
I have one table with company data. I have another table with documents. Often multiple per company. Im not sure how to best query both tables. If i do a left join i get Company 1 - Document 1 Company 1 - Document 2 Company 1 - Document 3 Company 2 - Document 1 Company 2 - Document 2 Company 2 - Document 3 Suppose i could read that out ok with a nice php loop which looks if the company changes, but that might not be the best option another approach would be to first get all companies Company 1 Company 2 and then with another query get all documents. What do you think is the best approach and is there any third option i didn't consider?
View Replies !
Do A Join Query And Display Results From Both Tables.
I was trying to do a join query and display results from both tables. I think I went wrong with the array syntax or was there another problem? PHP Code: $test=select * from tableA, tableB where tableAId = tableBId; $query=mysql_query($test, $link_id) or die(mysql_error()); while($get=mysql_fetch_array($query)) { echo "<tr class='style4'><td>".$query['tableA.ds']."</td><td>".$query['tableB.hy']."</td></tr>"; }
View Replies !
MySQL - Delete Across Two Tables. OT
i cant find the MySQL newsgroup, however i am hoping to pick up on some expert advice from php/mysql gurus here. I'm having some trouble performing a delete across two tables. The tables i have are: questionnaires (id, name); questionnaire_questions (questionnaires_id, id, name, qf_type) The questionnaire_questions table contains a list of questions for a specific questionnaire (indicated by the questonnaires_id). I would like to remove a questionnaire, and when doing so all corresponding questionnaire_questions who have matching id's (questionnaires.id = questionnaire_questions.questionnaires_id). I have this statement, however it only works if a questionnaire has questions... however i would like it to delete even if no questions exist for that specific questionnaire. The SQL i am using is: delete questionnaires, questionnaire_questions FROM questionnaires, questionnaire_questions WHERE questionnaires.id = questionnaire_questions.questionnaires_id AND questionnaires.id = THE_QUESTIONAIRE_TO_DELETE_ID
View Replies !
Want To DELETE Row FROM Multiple Tables
I'm looking to delete a single row from multiple tables where they all share a common column name, primaryKey, and a common value - $POST_[primaryKey]. Here is the code: $query="DELETE FROM assetinfo, emplhistory, income, newuser, preliminfo, primarydata, reoinfo, residencehistory WHERE primaryKey='$_POST[primaryKey]'"; $result=mysqli_query($connection, $query) or die (mysqli_error($connection). " <br> Could't run DB Query to Delete Borrower"); And here is the error msg I'm getting: 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 'WHERE primaryKey='jarreola2'' at line 1 Could't run DB Query to Delete Borrower Is my query valid? Any other ideas on how to delete a row from multiple tables?
View Replies !
Code Loop Through The Array And Delete Everything From Each Of The Tables
Will this code loop through the array and delete everything from each of the tables for a particular user. PHP Code: $tables = array ( "user", "user_info", "foo", "bar" ); foreach ($tables as $value) { DELETE * FROM $value WHERE user=$username echo 'user deleted from '.$value; } Also is there a better way to delete all user info across multiple tables? This seems as if it could be quite inefficient.
View Replies !
LEFT JOIN Versus INNER JOIN
I have the following line of Sql from order.php, $orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'"); i want to add to it the ability to query for the product manaufacturers name, i think i need the syntax for the following rules. lookup the product_model from products_table to retrieve the manufacturers_id. Use the manufacturers_id to lookup manufacturers_table to retrieve manufacturers_name. Could someone pls advise the correct syntax to perform such a query?
View Replies !
Join Vs Left Join
I have been looking though sites with these tutorials on them, i understand how to use them but i dont understand the difference between the 2 joins, i know that left join gives extra attention to the left table but what does that mean? it reads it first or what? and is there a certain time when you would use these or just preference?
View Replies !
LEFT JOIN And RIGHT JOIN
This is just going to make everyone think I know nothing about programming, but I have to ask it. What are LEFT JOIN and RIGHT JOIN used for? I haven't ever used a script besides a bulletin board that used "JOIN" and I am not sure what it was used for because I had the bulletin board several years ago. I was just on the MySQL website and saw that JOIN was grouped in with some of the optimization documents, and I firgured it was time to learn.
View Replies !
Delete Record - When The Delete Link Is Clicked The Next Page Is Blank And Nothing Is Deleted.
This is my "delete.php" and this "todo/delete.php?id=64" an example of a link to it generated from the index.php page. When the delete link is clicked the next page is blank and nothing is deleted. What have I done wrong? <? include("dbinfo.inc.php"); mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $id="delete from todo where id='$id'"; mysql_query($id); mysql_close(); ?>
View Replies !
Mysql Delete - $query = Mysql_query("DELETE
how would i do the following ? $query = mysql_query("DELETE notes, datestamp, abs_value, ID FROM absence_mgt WHERE datestamp='$date' AND ID='$vtc_login' ") or die(mysql_error()); im just getting "Unknown table 'notes' in MULTI DELETE" ?
View Replies !
How To Use Join?
I have to tables and I want to display the most recent row of both of them using a field, 'date', which I have in both. So I just want to be able to put two tables into one query.
View Replies !
Getting A Value From A Join
I have 3 tables which each contain a field named discount table1.discount table2.discount table3.discount I would like to do a join on the 3 tables and get the highest discount value of the three where table1.item = 'some number' How should I structure the query?
View Replies !
Using Join
I have 3 tables a, b, and c. I know what is in table a and I want the related info from table b and a. Is there some what to do that in one query? Code:
View Replies !
SQL INNER JOIN
I have this SQL statement; $confirmDetails = mysql_query("SELECT * FROM orders INNER JOIN reference ON orders.id = reference.box WHERE reference.refNum = $getRefNum"); ...which works, or did work fine. Today I have a strange problem... ... it will only fetch the 6 most recent entries from the database. Does anyone have any idea what this could be? I'm guessing its a wierd MySQL issue but I'm a bit in the dark on these sort of things as I am new to it all really.
View Replies !
JOIN Or Not To JOIN
I want to update a field so it's has the same value as a field from another table. Do I need to JOIN the two tables together to achieve this? This is the script I am trying at the moment, which does not include any JOIN. Example $result = mysql_query("UPDATE table2 SET field2='table1.field1' WHERE username ='$username'"); Is there anything wrong with this?
View Replies !
Help With A Join
I have a logging system setup. The main DB keep track of the time, page, and IP address of the person visiting. The second DB is a DB of IP addresses and who they belong to. I was able to do the successfully using this Code: SELECT * FROM `loggingdetailed` LEFT JOIN ip_addresses ON loggingdetailed.IP = ip_addresses.IP ORDER BY `record` DESC LIMIT 0 , 100 Now I want to remove my IP address from the loggingdetailed results. I tried using this: Code: SELECT * FROM `loggingdetailed` LEFT JOIN ip_addresses ON loggingdetailed.IP = ip_addresses.IP WHERE `loggingdetailed.IP` != CONVERT( _utf8 'x.x.x.x' USING ascii ) COLLATE ascii_general_ci ORDER BY `record` DESC LIMIT 0 , 100 But that is returning: "#1052 - Column 'IP' in where clause is ambiguous " So I tried where loggingdetailed.IP != .... it resulted in: #1054 - Unknown column 'loggingdetailed.IP' in 'where clause' What is the proper syntax to do what I want?
View Replies !
How To Do A JOIN
I still don't grasp how to do a join and would like someone to school me. Nothing I have read on MySQL.com or in books is getting through my head. I have two tables as follows (one stores info about some products, the other stores artist info who have created the products): oneofakind_items prodid (PK) artistid title desc price itemphoto dateadded oneofakind_artists artistid (PK) firstname lastname email photo symbol username password statement How can I write some SQL to query the DB asking for the two tables to be combined so I can display the artist info next to a particular item?
View Replies !
Without A Right Join
I am writing a blogging system to get better with MySQL and I am trying to grab all the categories, category id's, and the number of articles in each category all in one query. I've almost gotten it with a right join, but of course right joins will return NULL in the fields that don't match up. So I figure I need a different way to tackle this as it seems like it would be a common query. Here's what I've got: categories table id cat_title articles id art_title summary body ...etc posted_in table id art_id cat_id And here's my SQL SELECT COUNT(art_id) AS cnt, cat_id, cat_title FROM posted_in RIGHT JOIN categories on categories.id = posted_in.cat_id GROUP BY cat_title This returns cnt | cat_id | cat_title 0 | NULL | Broadband 1 | 6 | Business 0 | NULL | CSS 0 | NULL | Disaster Recovery 0 | NULL | PHP 2 | 3 | Security 1 | 7 | VPN's This is great, except everytime cnt is 0 the cat_id field is NULL. Also if anyone could help me (as a seperate query) how to get the same desired results without NULL, and the id and art_title out of the articles table.
View Replies !
My Join
I've been screwing around with this now for hours and I keep getting the same error. I know it is basically a join issue. I just need a new set of eyes. PHP Code: $result = mysql_query("SELECT states.state,colleges.state_id FROM states,colleges WHERE states.id=colleges.state_id and colleges.state_id={$_GET['cid']}"); And the error I'm getting is mysql_fetch_array(): supplied argument is not a valid MySQL result resource in. I'm using php version 4.4.6 and mysql 4.1.22.
View Replies !
Can't Get This Join
Code: select g.gameid, h.teamname home, h.email hemail, v.teamname visitor, v.email vemail from games g, join teams v on v.id = g.visitorid join teams h on h.id = g.homeid where (g.gamenum = 221)
View Replies !
Possible Join?
I'm having a hard time getting info from two tables. I have a common field in both called id. The first table consists of user info and is call customers. The second table is called csi and has related info such as word order id(woid) description etc. What I need is to display info from the customers table, custname, custadd, custphone ect. Then display all the work orders associated with the customers id. Then creat a link which when clicked bring up a view of the work order(woid). I can't seem to get this to work.
View Replies !
SQL Join
What im trying to do is , i have a table called shocc_active , it stores information about active users on my website. In another table shocc_users , i store user information. Im trying to build a "Who's Online" Page .. i could call all the data from shocc_active , but the only thing stored in their is the username , and the time, Id like to get other details about the users online from shocc_users , so that i can supply other details in my whos online page such as City , State , and Age , And Gender. is thiss possible? , I tried using PHP Code:
View Replies !
Join() Problems
What I am trying to do is capture the HTML output from a PHP generated script, and place it into a .html file. I am really close to having it complete but I am getting an error that I can not figure out. Any help would be MUCH appreciated! Here is my code (file is called mirror.php3):
View Replies !
Problem With JOIN
I am trying to work with JOIN. But it's not showing output. I send it with code. is there any error on my code? <?php $db_connect = mysql_connect('localhost', 'root', ''); $db_select = mysql_select_db(ཚk', $db_connect); $sql = "SELECT t1.name1, t2.dist2 FROM join1 t1, join2 t2 WHERE t1.nation = t2.nation"; //$sql = "SELECT name1,dist1 FROM join1 WHERE nation = 'india'"; $query = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($query, MYSQL_ASSOC)) { echo $row['t1.name1'] . "<br>"; } ?>
View Replies !
Mysql - SUM And INNER JOIN
Can anyone help with this query? Here is what I am trying to do: mysql_query("SELECT code.* FROM code INNER JOIN ratings ON code.id = ratings.codeid WHERE SUM(ratings.rating) >= 50 ORDER BY code.id DESC"); Obviously this won't work because you cant use SUM in the WHERE clause, but you can see what I am trying to do. Any ideas?
View Replies !
SQL Query - INNER JOIN ?
I have 2 tables, "Properties" and "PropertyLocation" Properties contains a field that refers to a field in PropertyLocation. I create a resultset from my querie, and then I display it. This works well, but I cannot show the field from PropertyLocation. This is the query I tried: Code:
View Replies !
SQL JOIN Problem!
I've got a problem with my SQL JOIN on my database stored on UNIX machine by Oléane !!!! (my SQL query is stored in php page) Here is my join's type : WHILE 'table1'.id = 'table2'.num_id Have you got an idea?
View Replies !
Dates Join
how can i join them: <input type="text" name="dd">15 <input type="text" name="mm">03 <input type="text" name="yyyy">2002 to return yyyy-mm-dd 2002-03-15.
View Replies !
Concat In A Join?
I think the following statement .... $ml_collect='SELECT *, DATE(CONCAT(field1, field2)) AS thedate FROM ml_lopp LEFT JOIN scfmforening ON (scfmforening.scfmnum = ml_lopp.scfmnum) LEFT JOIN ml_tidplats ON (ml_tidplats.loppnum = ml_lopp.loppnum) ORDER BY thedate' .....would work if "field1" and "field2" were in the table "ml_lopp" However, "field1" and "field2" are in the table "ml_tidplats" and accessed by "LEFT JOIN". So, how do I use "DATE(CONCAT(field1, field2))" in this case?
View Replies !
Table Join
The table WALKS lists walks where some records have photos available. To save searching through 750+ records to find those with photos, I have another table ALBUM with walks.walk_id listed in the column walk. The MySQL query that follows isn’t working. What am I doing wrong? Code:
View Replies !
Join 2 Files
I am trying to create a self extracting zip file ( on my Linux web server ) that will ultimately run on a windows OS. Research to date indicates that I need to create a zip file and then join it together with a win32 based sfx header stub ( which I have ). The resulting file can then be run on a windows OS, with the sfx stub running first and extracting the data from the zip file. Question is : how can I join the 2 files together into a new file ?
View Replies !
|
TRACKER
