kreut Posted February 19, 2011 Share Posted February 19, 2011 Hello, I'd like to perform 2 inner joins in one statement if possible (if not, I can always break it down but this would be less elegant). Basically, I've got 3 tables: questions --- contains homework questions for all assignments assignments_questions -- lists the questions for a given assignment submitted_answers --- lists the submitted homework questions and contains question_ids. I'm trying to generate a list of unanswered homework questions for a given assignment. First, I pull off the appropriate questions and their related information from a given assignment using: SELECT * FROM questions INNER JOIN assignments_questions USING (question_id) WHERE assignment_id = $assignment_id Then, for a given question, I need to see if submitted_solution from my submitted_answers is empty: if it is, then I'll list that question (the student has yet to answer the question). Any help on how to incorporate this piece would be appreciated. Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/ Share on other sites More sharing options...
Muddy_Funster Posted February 19, 2011 Share Posted February 19, 2011 SELECT table1.field1, table1.field2, table2.field1, table2.field2, table2.field5, table3.field1, table3.field2, table3.field3 FROM table1 INNER JOIN table2 ON (table1.joinField = table2.joinField), table1 INNER JOIN table3 ON (table1.joinField = table3.joinField) WHERE ...discision clause You can use as many INNER, LEFT and RIGHT joins that you want. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176720 Share on other sites More sharing options...
fry2010 Posted February 19, 2011 Share Posted February 19, 2011 I have a similair question so rather than post new thread thought would ask here. I am trying to get multiple column data from 4 different tables selecting the data from all the same auto_increment id. So I have a statement that looks like: $sql = "SELECT column-1a,column-1b,column-1c,column-2a,column-2b,column-3a,column-4a,column-4b,column-4c FROM table1,table2,table3,table4 WHERE table1.id = 1 && table2.id = 1 && table3.id = 1 && table4.id = 1"; So my questions are: 1) Do you think this is the correct way to obtain this data? 2) Performance wise, would it be better to split this into seperate statements or just request all in one statement? thanks... Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176817 Share on other sites More sharing options...
Muddy_Funster Posted February 19, 2011 Share Posted February 19, 2011 It sounds, from what you have described, that your table structure needs some serious review. The information shown would work with the use of a NATURAL JOIN accross the four tables. Using JOIN establishes a relationship between the tables used. The type of JOIN used dictates the type of relationship, NORMAL and INNER joins provide a 1:1 relationship, one matching record from table 2 will be matched against a single record from table 1. Read up on the use of JOINS, it's something that you will need to know if you are planning on doing anything even a little bit complicated with multiple table queries. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176823 Share on other sites More sharing options...
fry2010 Posted February 19, 2011 Share Posted February 19, 2011 ok thanks muddy. I think the reason is that I need to store a lot of data. I will use an example of shops. Lets say I have 20 fish and chip shops. I want to store data on prices, stock, shop names, shop website etc.. If you can imagine the amount of products they have it means a lot of different prices. So there is a ton of data. Could you recommend briefly a way to think about storing this data? Should I for example create individual tables for each data type, such as a table for price, stock , names, website etc.. If I do that I still think I will need to make a massive query, I dont see any way around it? Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176827 Share on other sites More sharing options...
fry2010 Posted February 19, 2011 Share Posted February 19, 2011 Basically I need to access all the data I have on one specific shop, bar a few things. I have read about joins etc, but I struggle to get my head around it until I start doing real world things with it. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176841 Share on other sites More sharing options...
fenway Posted February 19, 2011 Share Posted February 19, 2011 Show us your tables. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176847 Share on other sites More sharing options...
fry2010 Posted February 19, 2011 Share Posted February 19, 2011 create table broker_spread ( broker_id tinyint(2) unsigned not null, eurusd tinyint(3) unsigned not null, gbpusd tinyint(3) unsigned not null, usdjpy tinyint(3) unsigned not null, usdchf tinyint(3) unsigned not null, usdcad tinyint(3) unsigned not null, audusd tinyint(3) unsigned not null, nzdusd tinyint(3) unsigned not null, eurgbp tinyint(3) unsigned not null, eurchf tinyint(3) unsigned not null, eurjpy tinyint(3) unsigned not null, gbpjpy tinyint(3) unsigned not null, oilusd tinyint(3) unsigned not null, goldusd tinyint(3) unsigned not null, silverusd tinyint(3) unsigned not null, PRIMARY KEY (broker_id) ); create table broker_info ( broker_id tinyint(2) unsigned not null, micro_deposit_min int(5) not null, micro_deposit_max int(6) not null, standard_deposit_min int(6) not null, standard_deposit_max int(7) not null, leverage_min mediumint(4) not null, leverage_max mediumint(4) not null, bonus varchar(30) default'(no bonus)', promo_code varchar(30) default'(no promo)', PRIMARY KEY (broker_id) ); create table broker_votes ( broker_id tinyint(2) unsigned not null, trust int( unsigned not null, trust_votes int( unsigned not null, execution int( unsigned not null, execution_votes int( unsigned not null, charges int( unsigned not null, charges_votes int( unsigned not null, customer_service int( unsigned not null, customer_service_votes bigint( unsigned not null, ranking_score decimal(5,4) not null, PRIMARY KEY (broker_id) ); create table broker_storage ( broker_id tinyint(2) unsigned not null, heading varchar(30) not null, url_text varchar(30) not null, join_url text, alt_name varchar(40) not null, intro_1 text, intro_2 text, para_1 text, para_2 text, PRIMARY KEY (broker_id) ); In fact I need to grab all that data on second looks. So I should really use 'SELECT *' ... Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176849 Share on other sites More sharing options...
kreut Posted February 20, 2011 Author Share Posted February 20, 2011 Thanks for your help, Muddy_Funster! Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1176971 Share on other sites More sharing options...
fry2010 Posted February 20, 2011 Share Posted February 20, 2011 sorry for hijacking your thread kreut. I just assumed your issue had been solved, hopefully it has. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1177089 Share on other sites More sharing options...
kreut Posted February 20, 2011 Author Share Posted February 20, 2011 I appreciate your email. As fate would have it, the issue has been solved, so don't worry about it! Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1177126 Share on other sites More sharing options...
dflow Posted February 20, 2011 Share Posted February 20, 2011 ok i have a similar problem with a LEFT JOIN: SELECT agents.AgentUsername,proposals.ProposalID,proposals.ProposalType,proposals.ProductID,proposals.RequestID, proposals.CustomerLastName, proposals.CustomerFirstName_heb, proposals.CustomerEmail, proposals.CustomerCellphone, proposals.CustomerHomePhone, proposals.CountryName, proposals.CityName, proposals.RegionName, proposals.TotalNumber, proposals.DepartureDate, proposals.ReturnDate, proposals.timestamp,proposals.AgentID, proposals.StatusID, status_list.StatusID, status_list.StatusLabel, status_list.StatusImage, suppliers.SupplierName,proposals.SupplierID, country_list.CountryName, city_list.CityName,proposals.CountryID, proposals.CityID, proposals.RegionID FROM proposals LEFT JOIN status_list ON (status_list.StatusID=proposals.StatusID), proposals LEFT JOIN suppliers( ON proposals.SupplierID= suppliers.SupplierID), proposals LEFT JOIN city_list ( ON city_list.CityID=proposals.CityID) ,proposals LEFT JOIN country_list( ON country_list.CountryID=proposals.CountryID),proposals LEFT JOIN agents( ON proposals.AgentID=agents.AgentID) WHERE proposals.RequestID=%s ORDER BY proposals.RequestID desc i get an error Not unique table/alias: 'proposals' any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1177138 Share on other sites More sharing options...
fry2010 Posted February 20, 2011 Share Posted February 20, 2011 and I thought my table structure needed reviewing... At least though you are trying to use joins. Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1177196 Share on other sites More sharing options...
dflow Posted February 20, 2011 Share Posted February 20, 2011 anyone? Quote Link to comment https://forums.phpfreaks.com/topic/228186-multiple-inner-join/#findComment-1177269 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.