Multiple inner join

[kreut]

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.

[Muddy_Funster]

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.

[fry2010]

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...

[Muddy_Funster]

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.

[fry2010]

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?

[fry2010]

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.

[fenway]

Show us your tables.

[fry2010]

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 *' ...

[kreut]

Thanks for your help, Muddy_Funster!

[fry2010]

sorry for hijacking your thread kreut. I just assumed your issue had been solved, hopefully it has.

[kreut]

I appreciate your email.  As fate would have it, the issue has been solved, so don't worry about it! 

[dflow]

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?

[fry2010]

and I thought my table structure needed reviewing...

 

At least though you are trying to use joins.

[dflow]

anyone?