Unknown Column Error With Left Join Query
I can't get my left join operation working whenever my on clause includes a comparison to another table's column. I keep getting errors like this:
#1054 - Unknown column 'pr.risk' in 'on clause'
The query I am trying to execute is shown below. All was working well until I put that top join in. And yes there are risk and issue columns in the pr table....
View Complete Forum Thread with Replies
Related Forum Messages:
Unknown Column Error
I m receiving an unknown column 'foo' in 'where clause' error on the following PHP script. Can anyone shed any light as to why, because I am stumped. The following code creates a select box. function ItemsbyBrand() { print "<label>Search by Brand</label> "; print "<form action=""> "; print "<select id="manufacturer" name="manufacturer" onChange="top.location = 'brand.php?brand='+document.getElementById('manufacturer').value;"> <option value="0">Please choose...</option> "; $resultid = mysql_query("SELECT manufacturer FROM stock GROUP BY manufacturer"); if($resultid) { while($row=mysql_fetch_array($resultid)) { printf ("<option value="%s">%s</option> ", $row["manufacturer"],$row["manufacturer"]); } } print "</select> </form> "; } brands.php contains this <?php BrandResults($_GET["brand"]); and BrandResults is function BrandResults($brand) { $q = sprintf("SELECT stid,name,retailprice,catlist.cat FROM stock LEFT JOIN catlist ON stid = stock WHERE manufacturer=%s ORDER BY retailprice ASC", $brand); $resultid = mysql_query ($q); if($resultid) { BrandResultsName($brand); if(mysql_num_rows($resultid) > 0) { ProductTemplateCat ($resultid); } else { print "<p>The are currently no items within these search terms</p> "; } } else print mysql_error(); } function BrandResultsName($sres) { printf ("<h2>Items by %s</h2>",$sres); }
View Replies !
Strange 'unknown Column' Error
PHP $sql = "SELECT COUNT(*) FROM klanten WHERE username = '$_POST[newid]'"; $result = mysql_query($sql); if (!$result) { error('A database error occurred in processing your '. 'submission.If this error persists, please '. 'contact xxx.' . mysql_error()); and I swear there is a column called username. Still, I get an error saying that column username is not there. Anyone has an idea what might be wrong ? I just added the column. Does the server need time to refresh or sth ?
View Replies !
Error Code 1054 - Unknown Column
Have 2 tables in my data base. Can fill 1 successfully that has int and dec columns. The other with just text columns keeps throwing up 1054 errors. I am using the command insert into table_name (column_name1, column_name2) values (text_data_1, text_data_2); The error I get is 'ERROR 1054 Unknown column in field list'. I have looked for syntax, config. errors and trawled through my book but it does not appear obvious to me at least. I have attached a screenshot of the table description and the command sequence I am using plus the error message.
View Replies !
ERROR 1054 (42S22) At Line 1: Unknown Column
During a simple select statement in batch mode I receive the "ERROR 1054 (42S22) at line 1: Unknown column" error. This same select statment works just fine while logged in to mysql. ** LOGGED IN ** mysql> select lake_location from LakeInfo where lake_name='Clinton'; +---------------+ | lake_location | +---------------+ | Lawrence, KS | +---------------+ 1 row in set (0.00 sec) ** BATCH MODE ** root@server:/usr/local/scripts/lakeinfo# mysql --user=lake_user --password=xxx LakeData -Bse 'select lake_location from LakeInfo where lake_name='Clinton'' ERROR 1054 (42S22) at line 1: Unknown column 'Clinton' in 'where clause'
View Replies !
Unknown Column In Field List Error 1054
(dberrorMsg: Unknown column 'PRO0000652' in 'field list' dberrorNo: 1054) The 'PRO000652' is actually in the value list of the insert statement. The line below is part of the error showing the offending value. execute(insert into PROTOCOLIRB (PROTOCOL_ID,IRB_ID,APPROVAL_DATE,APPROVAL_NUMBER) values (247,1,'2007-08-01',PRO0000652))
View Replies !
Error 1054: Unknown Column Xxxx In WHERE Clause..
I'm trying to run this query $uneRequete = "SELECT * FROM Usager WHERE motDePasse = {$loginPassword}"; and I get this error message : Error 1054: Unknown column 'xxxx' in WHERE clause..... where 'xxxx' is the content of $loginPassword I don't know what is going on.
View Replies !
Error 1054 <42S22>: Unknown Column 'user' In 'where Clause'
am trying too delete a user from mysql database (which is used primarly for mediawiki). I typed in the following command in mysql command line client use wikidb; database changed Delete from user where User='Admin'; Too which I recieved the error Error 1054 <42S22>: Unknown column 'user' in 'where clause'
View Replies !
Left Join Query
I have this query SELECT user.user_id, email, password, full_name, company_name, first_login_date, last_login_date, first_draft_date, last_draft_date, first_submit_date, last_submit_date, rowc.* FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id WHERE user.user_type = 'USER' ORDER BY user.email Which returns all records in user table plus rowc records if there are matches in the user id. but user.user_id is null when there are no matching recordings in the rowc. Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?
View Replies !
Left Outer Join Query
I have noticed when I do a Left Outer Join in short form that many rows become missing as result of null values. i.e Left Outer Join (t1,t2,t3) ON (....) T3 being table with null values in It seems to be all to hard when I log a bug.
View Replies !
DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join. using DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id = items.item_id WHERE items.group_id =3 ) FROM item_colors does not work. how would be the right syntax? The SELECT part returns the items I want to delete from item_colors correctly.
View Replies !
Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for. I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip. Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY ' ' FROM table2 LEFT JOIN table1 ON table2.address = table1.address and table2.city = table1.city and LEFT(table2.zip,5) = table1.zip; My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.
View Replies !
Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack". What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query: SELECT s.id as id, s.title as title, s.users as creators, ROUND(AVG(r.rating), 1) as rating, r2.id as userhasrated FROM submissions AS s LEFT JOIN sub_ratings AS r ON r.subid = s.id LEFT JOIN sub_ratings AS r2 ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id LEFT JOIN packs AS p ON p.id = s.pack AND p.released = 0 WHERE s.state > -1 GROUP BY r.subid ORDER BY s.id DESC LIMIT 10 The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()). How can I fix my code so that it returns as many rows as I desire?
View Replies !
Query Trouble With LEFT JOIN
My query as it echoes out in PHP... SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP(shop_orders.date) as date, sales_reps.stage, sales_reps.status, ....
View Replies !
Duplicate Alias/table Error When Using LEFT JOIN
getting a error stating that I am using a duplicate alias/table.. PHP Code:  SELECT     concat(a.first_name,' ',a.last_name) AS name,     a.customer_id,     a.phone_mobile,     a.date_of_birth,     concat(b.first_name,' ',b.last_name) AS sponsor,     concat(c.first_name,' ',c.last_name) AS closing FROM     customer AS a,     (customer AS b LEFT JOIN a ON b.customer_id=a.sponsor_customer_id)     (customer AS c LEFT JOIN a ON c.customer_id=a.closing_customer_id) WHERE a.first_name LIKE 's%' ORDER BY a.first_name ASC;Â
View Replies !
Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query? The original query joins three tables but even if I narrow it down to one it still takes a long time to execute. $query = "select distinct materials.* from materials"; $query .= " left outer join materials_products on materials.material_id = materials_products.material_id"; There's 914 rows in the materials table and 1348 row in the materials_products table Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?
View Replies !
Mysql 5.x And "unknown Column" Error
I read a similar post here about putting the table name in parentheses before a LEFT JOIN because mysql 5x is more strict than 4.1. However, it doesn't seem to be working. The original error I got (when the web host decided to upgrade their mysql version across the board without really checking with anyone!) was this: Unknown column 'products.category' in 'on clause' So I'm stumped. I've checked and rechecked my syntax but either I get the above error or a general syntax error. I'm mostly a hack programmer, so I won't pretend to understand everything here ..........
View Replies !
Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example: SELECT t1.c1, t2.c2, SUM(t3.c3) FROM t1 LEFT JOIN t2 ON t1.c1=t2.c1 LEFT JOIN t3 ON t1.c1=t3.c1 GROUP BY t1.c1 The problem is that I also want to specify a condition for selecting records from t1: WHERE t1.c1='x' so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.
View Replies !
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.
View Replies !
Unknown Column
i was using an older version of mysql on my laptop and working on a portal project, all was going well, no problems even using php5, i have now upgraded to mysql 5.0.18 and when i try and access the profile page of the forum i get this error.... i'll past the query first then the error i am now getting. Quote:if ($user_result = db_fetch_array(db_query("select user.*,groups.online_template_large,session.url,session.sessionid from user,groups left join session on session.userid=user.userid where user.userid='$_REQUEST[id]' and user.groupid=groups.groupid order by session.lastactivity desc limit 1")))
View Replies !
Unknown Column In 'on Clause'
I have seen this a couple of times and what throws me for the loop is the column IS in the table. Unknown column 'cm.state_id' in 'on clause' select cm.*, o.userid,c.printable_name country_name, s.printable_name state_name, email_add as owner_email from b2gcar_mst cm, b2gcountry c, b2gowner_mst o left join b2gstate s on (cm.state_id=s.state_id) where cm.country_id=c.country_id and cm.owner_id=o.owner_id and cm.cid=Ɔ'
View Replies !
Unknown Column XXX In 'on Clause' - :(
I use mySql 5 and i get this particular error error: Unknown column 'p.ID' in 'on clause' when i'm using the below statement "select p.title, p.url, p.ID, p.category, p.submitted_user_id ,pc.cat_title ,u.username, u.ID, pc.ID as cat_id, from posts p, post_cats pc, users u left join post_votes pv on pv.post_id=p.ID left join post_comments pcom on pcom.post_id=p.ID where pc.ID=p.category and u.ID=p.submitted_user_id ";
View Replies !
Unknown Column (assigning Using AS)
SELECT u.user_id, u.user_name, ( SELECT COUNT(friend_from) FROM friends WHERE friend_from = u.user_id ) AS friends_added, ( SELECT COUNT(user_referered) FROM users WHERE user_referered = u.user_id ) AS friends_referered, friends_added + (friends_referered * 10) AS points FROM users u Quote: #1054 - Unknown column 'friends_added' in 'field list'
View Replies !
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.
View Replies !
Table Join Query Error
The query’s work fine when I try them separate. $query = "SELECT id,loginName,car,yearmod,city FROM Member UNION SELECT imgName1 FROM Gallery"; $result = mysql_query($query) or die ("DOH!"); I have tried with and without the "(" ")" phpinfo says my MySQL Client API version is 4.0.21 so that shouldn’t be a problem, right?
View Replies !
Unknown Column In Field List
I'm trying to update a text field upc12 in my universal table with a text field upc20 from my newtable where universal table and the newtable have the same UPC number... UPDATE universal SET universal.upc12 = newdata.upc20 WHERE universal.upcnumeric = newdata.upcnumeric; SQL Error: Unknown column 'newdata.upc12' in 'field list' I have verified that newdata.upc12 is intact.
View Replies !
Unknown Column 'p.insurance_id' In 'on Clause'
The other day we updated our live server with the latest stable release for fbsd, release 5.0.18, and we began to get 'unknown column in on clause' errors, error code #1054. At first I thought it was a problem with the installation, but I duplicated the exact errors on my personal windows xp pro pc by upgrading to release 5.0.18 from 5.0.11. On another query we get the following error: Line 258: Unknown column 'p.insurance_id' in 'on clause'. We tried creating a new field, called insurance_id_02, in the patient table and changing the code to look for that field instead of the p.insurance_id field in the on clauses, but mysql returned the same error. Any help would be greatly appreciated. I don't know if this is a bug in the new release or a strange quirk in our db.
View Replies !
Unknown Column 'some_value' In 'where Clause'
As I navigate through my (still built..) web site, i've created a link that directs to : /show_title?action=show&id=".title_id." that leads to a page where I run the query: $id = $_GET['id']; "SELECT title_name from titles WHERE title_id = $id" and i get: --> Unknown column 'CDA15FD' in 'where clause' although when I move the cursor on the link I get: /show_title?action=show&id=CDA15FD and CDA15FD DOES exist in my db!
View Replies !
Unknown Column 'test' In Order By
These works as expected: SELECT 2 AS test ORDER BY test SELECT 2 AS test ORDER BY 2-1 But this errors, saying: "Unknown column 'test' in Order By." SELECT 2 AS test ORDER BY test-1 Is this a bug or whats the deal? Tested in 5.0.27.
View Replies !
Unknown Column 'NAN' In 'field List'
I just ran into this issue, and I am hoping that some of you may be able to help. I am developing a web-based PHP application with a MySQL back-end. I have a testing and a production environment. The only discernable difference between the two is that the testing environment runs MySQL 4.1.14 and the production environment runs 5.0.2. I made some modifications to an upload page (with just a bunch of instert statements) today. It works flawlessly on the testing system but when I push it out to production, I get this error message: Unknown column 'NAN' in 'field list' Any ideas? I expect some of you would suggest that I upgrade my testing environment so that it matches production. Although I could do this, in this case the application may give me this error on both systems and I would be no closer to the solution
View Replies !
Unknown Column - Variable Names As Strings?
I'm having some trouble inserting records into my DB. The values being written to the database are either the titles of my variables or I get an "unknown column" error. The following is a printout of what's actually getting saved to the database, followed by the values that are supposed to be sent to the database (from form data): Code:
View Replies !
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)
View Replies !
#1054 - Unknown Column 'trxtal.groupno' In 'on Clause'
running this query iam getting the following error: select trxtal.groupno as groupn,artwork.artworkno as artworkno,count(trxtal.groupno) as grp from trxtal, mstcus LEFT JOIN artwork ON (trxtal.groupno=artwork.groupno and trxtal.classcode=artwork.classcode) where trxtal.groupno=mstcus.groupno group by mstcus.groupno having sum(artwork.artworkno)>0 #1054 - Unknown column 'trxtal.groupno' in 'on clause' This query was working fine before with my old version of mysql, when i upgarde i am getting this error....pls help MYSQL version:5.0.51b Apache version :2.2.8 PHP version: 5.2.6
View Replies !
Upgrading: Replacing Commas With "INNER JOIN" In LEFT JOIN Queries
I've just upgraded from 4.1 to 5.0 and I'm very scared. So, no more comma-separated table names in queries with LEFT JOIN clauses are allowed? ( http://forums.devshed.com/mysql-help-4/having-unknown-column-in-on-clause-error-323495.html) Each of those commas has to become ' INNER JOIN '. I have almost 400 left join queries spread out over a couple hundred files. I'm sure it's for the best, but oh is this task going to hurt! It looks like even a regular expression search & replace solution won't be feasible. I'm sure I'm not the first person who had to do some mass replacing. Any suggestions? Am I dreaming thinking there might be a variable that can be set that will allow the "old" format? I'm sure I'm in store for other issues, but are any of them major syntax changes like this one?
View Replies !
Unknown Why This Error Is Happening
unknown column 'g.id' in 'field list' in this SQL? CREATE TABLE teamScoresH2H ( SELECT t1.id as team_1_Id, t2.id as team_2_Id, COUNT(DISTINCT g.id) AS nbrOfGames, SUM(IF(g.homescore > g.visitorscore, 1,0)) AS Wins, SUM(IF(g.homescore < g.visitorscore, 1,0)) AS Losses, SUM(IF(g.homescore = g.visitorscore, 1,0)) AS Ties, SUM(IF(g.homescore > g.visitorscore, 3, IF(g.visitorscore = g.homescore, 1, 0))) AS Score FROM teams t1 INNER JOIN teams t2 ON t1.id <> t2.id LEFT JOIN games g ON (g.homeid = t1.id AND g.visitorid = t2.id) OR (g.homeid = t2.id AND g.visitorid = t1.id) WHERE (t1.division='AA' OR t2.division='AA') AND (t1.division='AA' OR t2.division='AA') AND (g.homescore not in (999,777) or g.homescore is null) GROUP BY t1.id, t2.id )
View Replies !
Left Join Help
Using MYSQL 4.0.13, debian linux create table members ( id unsigned int autoincrement, name ) create table activity ( id unsigned int autoincrement, description ) create table history ( id unsigned in autoincrement, date date, member_id unsigned int, activity unsigned int ) What I need: 1) only records for a particular date 2) there should be at least one record for each activity 3) there may be multiples of the same activity on a given date 4) there may be multiples of the same member on a given date 5) not all members will be listed 6) the members.name result field may be NULL SELECT history.date, activity.description, members.name ??? WHERE history.date = 'YYYY-MM-DD'
View Replies !
Using LEFT JOIN
I have two tables which I am trying to perform a left join on. table 1: master columns | datatype -------------------- masid | int(4) maDesc | varchar(200) maNote | varchar(200) table 2: slave columns | datatype -------------------- asid | int(11) masid | int(4) uid | int(11) cdate | datetime title | text my query is as follows SELECT * FROM `master` LEFT JOIN `slave` ON master.masid = slave.masid WHERE slave.uid = ? OR slave.uid IS NULL ORDER BY master.masid DESC LIMIT 0, 30 I want to be able to display a row for each master.masid and then, depending on whether a slave row exists that matches display a link to create on (this is a php/mySQL project). Problem is it doesn't quite do what I want. It only seems to return the one row with the highest master.masid if no matching slave row exists for it, rather than listing all the masid rows for which no matching slave row exists. It returns all the matching slave rows.
View Replies !
A LEFT JOIN With LIKE?
I would like to use a "LIKE" condition in my LEFT JOIN statement. At the moment I have something like: SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url Now I want to include the subpages from i.url. So if p.url is "www.here.com/page" it should also match if i.url is "www.here.com/page/subpage.html" So the new command should look something like: SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url% Can this be done? How?
View Replies !
A LEFT JOIN With LIKE
I would like to use a "LIKE" condition in my LEFT JOIN statement. At the moment I have something like: SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url Now I want to include the subpages from i.url. So if p.url is "www.here.com/page" it should also match if i.url is "www.here.com/page/subpage.html" So the new command should look something like: SELECT * FROM page AS p LEFT JOIN item AS i ON p.url=i.url% Can this be done? How?
View Replies !
Left Join With LIKE
hey I'm trying to join two tables on whether one text field is inside another text field, is there someway to do this? something along the lines of table1.field1 LIKE '%' & table2.field2 & '%'
View Replies !
LEFT JOIN
SELECT br.rid, br.title FROM {booking_resource} br LEFT JOIN {booking_schedule} bs ON br.rid = bs.rid WHERE bs.timestamp != [timestamp for a new booking];
View Replies !
LEFT JOIN With MIN() - Please Help
I just can not get my head around this, so any help is appreciated. I am building a real estate web site and need to look up properties to list them. I need to get an image from the jos_mh_images table if one exists. If not - I still want to get the content from jos_mh_properties - thus the LEFT join. Here is a simplified version of my query: SELECT p.*, MIN(i.image_name_id), i.image_ext FROM jos_mh_properties AS p LEFT JOIN jos_mh_images AS i ON p.id = i.property_id WHERE p.published = 1 GROUP BY p.id LIMIT 0, 10 My issue is that the query returns the correct MIN i.image_name_id but the returned i.image_ext is not from the same row. I would like to select the whole row from jos_mh_images where i.image_name_id is smallest and property_id = p.id My original query has lots of other LEFT JOINS that works great - they have been removed to make the question easier to understand.
View Replies !
Using A WHERE With LEFT JOIN
My problem seems pretty simple, but I'm having a tough time figuring out a SELECT statement that will do what I want. Basically, I have two tables that look like this: table1 userid 1 2 3 4 5 6 7 table2 myid addedid 2 4 2 5 What i need to do is find a way to list the users in table1 that are NOT listed in 'addedid' in table two. This is the basic select statement that I'm using: SELECT table1. * , table2. * FROM table1 LEFT JOIN table2 ON table1.userid = table2.addedid Which lists the results like this: userid myid addedid 1 null null 2 null null 3 null null 4 2 4 5 2 5 6 null null 7 null null What I want to do is display only the results that have "myid" and "addedid" as null. I tried adding "WHERE users.userid!=adds.addedid" to the original SELECT statement's ending, but it doesn't work. I also tried adding "WHERE "myid=null", but that doesn't work either. ?
View Replies !
Right And Left Join
So I have Table named Global and one named Personal. i need to Get All Records from both tables that match a single userid. These tables may have a linking attribute (globalid to id) But it may not exist as well. The only way I have figured out how to get the correct data is to utilize 2 seperate queries. Can I get these to run a one? CREATE TABLE `global` ( `id` int(11) unsigned NOT NULL auto_increment, `userid` int(11) unsigned NOT NULL, `value` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8245; CREATE TABLE `personal` ( `id` int(11) unsigned NOT NULL auto_increment, `userid` int(10) unsigned NOT NULL, `globalid` int(11) unsigned default NULL, `value` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=379 ; Now here is some Data INSERT INTO `global` (`id`, `userid`, `value`) VALUES (2442, 13, 630), (3017, 13, 0), (3108, 13, 78), (3109, 16, 2220), (3110, 16, 2220); INSERT INTO `personal` (`id`, `userid`, `globalid`, `value`) VALUES (324, 13, 2442, -5), (367, 13, 3017, 10), (378, 13, NULL, 25), (336, 12, NULL, 2543); And here is the current queries I am running. SELECT g.value, p.value as PValue FROM `global` g LEFT JOIN personal p ON p.globalid = g.id WHERE g.userid = 13 SELECT g.value, p.value as PValue FROM `global` g RIGHT JOIN personal p ON p.userid = g.userid AND p.globalid = g.id WHERE p.userid = 13 AND g.id is null
View Replies !
|
TRACKER
