SEARCH YOUR SOLUTION HERE  

INNER JOIN w/ some data not available

I have three major pieces I'm trying to accomplish, if possible, with a single mySQL query dealing w/ joining data from multiple tables. Here's the first piece I'm having a problem with. I'm simplifying to highlight just what I need.

Goal
*********
Joining two tables, even if one in particular happens to not have associated data. Specifically, I have a table called events and a table called practitioners. Not all events will have practitioners, but for those that do, I want to display their practitioners. For those that don't, the event should still be shown, although with a blank practitioner.

Table Structure/Content
********

events
**
ID, date, practitionerID
1, 20091106, 0
2, 20091107, 8

practitioners
**
ID, name
8, Jeremy


Deed Outcome
**********
20091106, ''
20091107, 'Jeremy'


Current Code
*********
PHP Code: $query="SELECTevents.ID,events.date,events.practitionerID,practionerers.nameFROMeventsJOINpractitionersONpractitioners.ID=events.practitionerIDORDERBYevents.date";


Result
*********
20091107, 'Jeremy'


Problem
*********
Because there is no matching practitionerID to the first event row (with a practitioner ID of 0), it fails to produce that row of data. I want it to return the data and just leave practitioner.lastName blank.

Posted On: Monday 29th of October 2012 05:43:33 AM Total Views:  342
View Complete with Replies




Related Messages:

How to run multiple inner join when one condition failed?   (138 Views)
Hi I have stored procedure(selecting values from DB in mysql)where i have written four inner join statements. After inner join there is a where clause. In this inner join statement any one join failed then this select statement will not execute. i think i need to check some column name which is in inner join. if this column is NULL then its not selecting values. How to avoid this I mean if one join fails it should execute other join statements and fetch the values. How to achieve this please help me in this.I am working on Mysql .. Not in SQL Server.
Why does ORDER BY in inner query make no difference to final result?   (150 Views)
Thanks for reading: There are two tables named t1 an t2 t1 has columns CommentID (unique), UserID, QID, and PostTime. t2 has columns UserID (unique) and Username Example for t1 CommentID|UserID|QID|PostTime 1---------|10----|1--|2011-01-10 2---------|10----|1--|2011-01-09 3---------|10----|2--|2011-01-01 3---------|10----|2--|2011-01-15 Example for t2 UserID|Username| 10----|patrick-| For a given UserID, I would like the result set to include one entry from each QID that is linked to that user. I would like the QID returned to be the one with the greatest PostTime from its QID group. finally, the result set should be ordered by PostTime DESC across all QID values. So with the examples above, if we search for UserID 10 the return set should be UserID|Username|QID|PostTime 10----|patrick-|2--|2011-01-15 10----|patrick-|1--|2011-01-10 I'm having trouble with ordering across all QID values and I can't figure out what's wrong with my query. Below is my query: SELECT * FROM ( SELECT t1.UserID, t2.Username, t1.QID, t1.PostTime FROM t1 LEFT JOIN t2 ON t1.UserID = t2.UserID WHERE t1.UserID = 10 ORDER BY t1.PostTime DESC ) As t3 GROUP BY QID I thought that the inner querie's results woul be ordered with highest post time on top, then when the outer query groups the results by QID I would end up with unique QID values in the result set, with the highest Post Time on top. The inner query works well (highest post times at the top) but unfortunately the final results are not ordered by PostTime DESC, and I'm not sure what I'm missing. Help pleas
Beginner multi-join question   (105 Views)
hi there 3 tables for a small trading system, the trades themselves are held in table TRADES, as: seller_id buyer_id item_id quantity cost currency all INT. I want to display pending trades, using a 3-way join to map IDs to names in tables USER and ITEM. So far I've got: Code: SELECT users.name AS seller, items.id AS item_id, items.name AS item, trades.item_id AS trade_id, trades.quantity AS quantity, trades.cost AS cost, trades.currency AS currency FROM (trades INNER JOIN users ON trades.seller_id = users.id) INNER JOIN items ON trades_id = items.id WHERE trades.buyer_id = xxx the first join works fine, but as soon as I pass the result to the second it fails; returns zero data. I'm braced for it being a real noobie problem (in fact I'm hoping it is, easy to fix = good) but I can't see it (probably because I am in fact a noob at these things). Help
Adding yet another inner join   (123 Views)
I already have the following query: Code: "SELECT product.product_id, product.name, product.description, product.fabric, product.weight, product.price, product.image_file_1, product.image_file_2, sizedata.sizes, colourdata.colours, colourdata.pictures FROM product INNER JOIN ( SELECT product_size.product_id, GROUP_CONCAT( size.size ) AS sizes FROM product_size INNER JOIN size ON size.size_id = product_size.size_id GROUP BY product_size.product_id ) AS sizedata ON sizedata.product_id = product.product_id INNER JOIN ( SELECT product_colour.product_id, GROUP_CONCAT( colour.colour ) AS colours, GROUP_CONCAT( product_colour.colour_image ) AS pictures FROM product_colour INNER JOIN colour ON colour.colour_id = product_colour.colour_id GROUP BY product_colour.product_id ) AS colourdata ON colourdata.product_id = product.product_id WHERE product.product_id = $productId"; But i need yet another join, I need to join product_id with another product_id in a table called product_image, this has two fields, product_id and product_image. I tried a simple inner join but this prevented the whole thing from working. Can someone please help me regards, Andy
Join 3 tables- inner, outer, left?   (134 Views)
I have the following three tables (*= primary key): user_address *address_ID user_address address_ID *user_ID address_ID user_name *user_ID user_name I need to: Code: SELECT ALL from user_name AS n user_address AS a, address_ID as i WHERE n.user_ID = i.user_ID i.address_ID = a.address_ID a.user_address = 'entered value' I know I need to use joins, but not sure which ones and syntax Any assistance is greatly appreciated...
Mysql querie help for a beginner   (117 Views)
I am trying to get result and display them in a particular format. Im a beginner and hope below discription is clear. Table Structure is like mentioned below: ID| Date | Report| Start time | End Time REPORT Attribute hold any of the three values(Productivity, Monitoring, Break). I want to display the output in below two formats between two selected dates from a form. Below I've posted the exact format that I need for daily report and monthly report. I don't need the start and end time for monthly report. Code: Below is the daily report Date | Emp ID | Emp Name | Report | Frequency | Language | Production Training | Meeting| Feedback | Others | Time Utilization (Endtime - start time) 11/22/2011 | 450220 | Messi | client Name | daily/weekly |english | Yes | | | 1:30 11/22/2011 | 450223 | Thomas | client Name | daily/weekly | english | | | | | Yes | 0:30 11/22/2011 | 4502269 | Lisa |client Name | daily/weekly | Spanish | | Yes| |8:30 Monthly report will be the same as it only display the montly time for "time Utilization". However, the time utilization should be in "hours: Minutes" and not in Days:Hours: Minutes.
Database Design help (beginner)   (111 Views)
: nvm i got help elsewhere
Query help - select a winner (4 choices for vote)   (127 Views)
hey all, was hoping someone could help me out with this query i have a list of users that can vote on a contest. there are like 20 things they can choose from, but they can only vote for 4. i need to select a winner providing they have voted for the 4 correct answers. each answer is written to a table as a separate field contest_answers is the table that holds these values user_id holds the users id question_id holds the answer (i.e. 1, 3, 8, 9) which are the ids for those questions so if the correct answers have the ids: 1, 2, 3, 4 ... i need to select all the entries in contest_answers that have these. any help is much appreciated. cheers!
mysql: left vs inner joins   (137 Views)
I'm sure this has probably been asked before and, don't worry, I have searched the forums and found some answers. BUT does anyone know any links to exampls of the two joins I've got explanations, sure. But I feel I'd better understand what left joins do (over inner ones) if I could see an example of the output you'd get on a simple DB. Unless anyone has the time or inclination to write one
inner join of two tables   (129 Views)
hello to all i have two tables in MYsql one is tab1 and second is tab2 in tab1 the dtaa is like sno class assid throid 1 II 4 7 and in tab2 sno id name 1 4 pace 2 7 turns Now i want to genearte output table like in tab1 where assid and throid is coming i want to take the name from tab2 according to that id..tell me how to make innner join
Help for a beginner   (118 Views)
Hi !! I want to create a table that stores all the films I have in my server.The difficult part is that I want to add a field where the users that are watching the film are stored. E.g.: FILM -------------------- USERS WATCHING Jurassic Park ------------- Ellen, Jack Sicko -------------------- Patrick Is it possible to create such a dynamic field that has unknown size How can create a filed which stores several values (it is like storing an array) Probably I should use another table but I don't know... any suggestion would be appreciated.
Using inner and outer mysql join in a query   (123 Views)
, I am trying to get a query to work although it is probably not the best way to do it. What I ultimatly need to do is I have 5 performance measures where I need to grab all staff who where hired on a specific day and then list the groups total average for each month strating this the previous month and going back 6 months. I have an employees table where I list the hire date and also a groupwiseid field where the groupwiseid field is what links all the tables together. So I need to do a query on the employees table and pull s groupwiseid where their hire date is x and then connect to all the 5 performence measures tables and get the total avgh of the group for each of the last 6 months So I was thinking I could do each month in just one long query using inner and outer joins. I started to work on this query but it does not even work PHP Code: $getpeers=mysql_query("SELECTe.RoleID,e.DateHiredFROMEmployeesaseinnerjoinRoleID='$origroleid'ANDDateHired='$orighiredate' outerjoinselectavg(mtd_s.Compliance)FROMMTD_SurveyTranferasmtd_sinnerjoinGroupwiseID='$GroupwiseID'ANDMONTH(Date)='$sixmonths'ANDMTD='1'GroupByComplianceouterjoinselectavg(mtd_tbox.TopBox)FROMMTD_TopBoxasmtd_tboxinnerjoinGroupwiseID='$GroupwiseID'ANDMONTH(Date)='$sixmonths'ANDMTD='1'GroupByTopBox"); $peersrow=mysql_fetch_array($getpeers); $comp=$peersrow['Compliance']; $tb=$peersrow['TopBox']; so the above produces a Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource error message
A beginner's question   (145 Views)
what data type should i use for password
Results of inner join into a new table   (122 Views)
Ive got a DB with 2 tables, one is a contact table, the other is one with postcodes and suburbs. I want to perform and inner join on the two, then put the results of the query into another table so i can export it for a mail merge. Code: CREATE TABLE Merge ( FirstName VARCHAR(50) NOT NULL , LastName varchar(50) NOT NULL , Title varchar(5) NOT NULL , Address varchar(150) not null , Suburb varchar(20) not null , State varchar(3) not null , DateJob date not null , Postcode int(4) ) ENGINE=MyISAM; insert into merge (firstname, lastname, title, address, suburb, state, datejob, postcode) values (1weekletters.FirstName, 1weekletters.lastname, 1weekletters.title, 1weekletters.address, 1weekletters.suburb, 1weekletters.state, 1weekletters.datejob, newcastlepostcodes.postcode); SELECT 1weekletters.firstname, 1weekletters.lastname, 1weekletters.title, 1weekletters.address, 1weekletters.suburb, 1weekletters.state, 1weekletters.datejob, newcastlepostcodes.postcode FROM `1weekLetters` INNER JOIN `NewcastlePostCodes` ON 1weekLetters.suburb = NewcastlePostCodes.name;
Page 2 - Query help - select a winner (4 choices for vote)   (145 Views)
select distinct user_id as pass_id from ( select user_id , answer , count( question_id ) as correct from contest_answers where question_id in ( 110, 113, 121, 109 ) group by user_id , answer having correct = 4 ) as d
SQL inner join question   (177 Views)
, I am trying to write a query but am having difficulty. I need to find every person in table A that has a certain sport selected. My boss wants some fields displayed in 2 other tables for his report that I match on the person's id. But they may or may not have a record in the other 2 tables. My query isn't working if the person doesn't have an adjoining record in the other 2 tables. How do I fix this Here is my sql code.. SELECT A.jersey_num, A.first_name, A.last_name, A.position, A.height_ft, A.weight_lbs, A.dob, A.academic_year_prog, A.academic_year, A.city, A.province, B.HighSchool, C.LastYrSchool, C.YrsOfEligUsed FROM basic A INNER JOIN athletebio B on A.MacID = B.MacID INNER JOIN eligform C on A.MacID = C.MacID WHERE sport1 = '$sportID' OR sport2 = '$sportID'
join vs. inner join vs. implied join = different results ??   (193 Views)
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 ) = '2007-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 ) = '2007-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 ) = '2007-01-04' AND o.orderSTATUS = 300 yields 157 ANY ideas why they are different
Help with structure - primary key - inner join (i think)?   (335 Views)
version 4.1 / using php admin Hi all I basically have a no. tables storing photographs and need to understand how I can get this structure right I think a inner join is the right way What I want is to be able to select the 'date' and 'photo_set' and display all the photos What I have so far: gallery_information - date / date, primary key - title / varchar 90 photo_information - photo_id / primary key - photo_set gallery_photo_relation - date - photo_set
Beginner SQL question   (120 Views)
, I'm having trouble writing a query. I'm looking at click-stream type date. My table is simple, containing 3 columns: user, date and event. What I'm trying to do is have MySQL spit me out the 3 events prior to a specific event occurring, by user. So, for example, I want to see a user's 3 events before he performs an event called "button". I can understand how the logic might work: 1. Look for the "button" event and return the date. 2. Identify all events prior to that returned date, by user 3. Sort those events 4. Limit the results to 3 ...but, my SQL skills aren't sharp enough to solve this (yet!)
Trouble with inner outer joins   (131 Views)
all, So I have looked around and found a couple of suggestions that sort of worked. I have two tables, categories and pics_cats. categories: id, name pics_cats: picId, catId What I am trying to do is pull ten categories, whether a picture exists or not and have a count of pictures within those categories. This is what I have: SELECT COUNT(*), categories.category FROM pics_cats INNER JOIN categories ON pics_cats.catId=categories.id GROUP BY pics_cats.catId ORDER BY RAND() LIMIT 10 This gets me ten random categories and how many pictures in those categories. I've tried changing the INNER JOIN to RIGHT OUTER JOIN. I do get ALL the categories but for the ones that have no pictures it shows the number of categories minus 1 instead of zero. Any suggestions