sql help

G

Got Haggis?

Veteran XX
what is the best statement to do this

I can only pass a topic_id to the sql statement

I have 5 tables

topics, materials, materials_topics, languages, and producers

topics has topic_id, and topic
materials has material_id and a bunch of other text fields i need
materials_topics has material_id and topic_id
languages has material_id and language
producers has material_id and producer

So, what I need to do is, based on topic_id (for example lets say a topic id of 2) I need to get all materials that have a topic_id of 2, then get its material_id and display all the info from the 3 different tables (materials, languages, and producers) that has the same material_id

i tried doing this using a LEFT JOIN and it crashed the server, so i guess i'm off somehwere. i suck at sql anyway. something like this

Code: 
SELECT materials.*, languages.*, producers.*, materials_topics.material_id FROM tools_materials
			LEFT JOIN materials_topics
			ON materials.material_id = materials_topics.material_id
			WHERE materials_topics.topic_id = '$topic_id' AND
                        languages.material_id = materials_topics.material_id AND 
                        producers.material_id = materials_topics.material_id

how can i fix this to run and not crash the server :lol: ?
 
Freenode IRC
#mysql
they will know probably better than anyone on this board

also, joins should work, and you can cascade joins so you can join more than one table to your result set, but i don't have the brainpower or energy to help you today :(

trust me, the #mysql guys will be able to help
 
Code: 
SELECT 
    materials.*, languages.*, producers.*
FROM 
                materials_topics
JOIN          materials ON materials.material_id = materials_topics.material_id
LEFT JOIN   languages ON languages.material_id = materials_topics.material_id
LEFT JOIN   producers ON producers.material_id = materials_topics.material_id
WHERE 
   materials_topics.topic_id = '$topic_id'
 
But wait, are you trying to query the tables with a parameter? That would be a little different. You would also need a parameter in your WHERE clause.
 
Something like this ---

Code: 
SELECT     materials_topics.*, languages.*, Materials.*, producers.*, Topics.topic_id AS TOPIC
FROM       Topics INNER JOIN
              materials_topics ON Topics.topic_id = materials_topics.topic_id LEFT OUTER JOIN
              languages ON materials_topics.material_id = languages.material_id LEFT OUTER JOIN
              producers ON materials_topics.material_id = producers.material_id LEFT OUTER JOIN
              Materials ON materials_topics.material_id = Materials.material_id
WHERE     (Topics.topic_id = '1')

You can use a parameter in the where clause to change what topic_id you are looking for or just generate a string to stick in there...
 
Last edited:
okay shoddys example works great. However something really strange happens when I add another table. I forgot I had another table, called components, which contains material_id and components. So I figured I could just modify the statement to be
Code: 
SELECT 
    materials.*, languages.*, producers.*, components.*
FROM 
                materials_topics
JOIN          materials ON materials.material_id = materials_topics.material_id
LEFT JOIN   languages ON languages.material_id = materials_topics.material_id
LEFT JOIN   producers ON producers.material_id = materials_topics.material_id
LEFT JOIN   components ON components.material_id = materials_topics.material_id
WHERE 
   materials_topics.topic_id = '$topic_id'

this only returns 1 result, when it shoudl be returning 5. If I take out the reference to the component table stuff, then it returns the correct 5 results, just without component info. Why is it doing this?
 
He's using PHP (I think) and accessing the DB directly from the scripting language.. $topic_id is his parameter (variable in php).
 
Make sure to check if you should be using "left outers" in case one of the tables has no entries. If all tables will have entries, it might not be a problem.
 
These are all about the same sql, but mainly a matter of syntax and personal style i'm guessing:

Code: 
SELECT * FROM Topic t
inner join materials_topic mt on t.topic_id = mt_topicid
inner join materials m on m.material_id = mt.material_id
inner join languages l on m.material_id = l.material_id
inner join producers p on m.material_id = p.material_id
where t.topic_id = 1
 
Well, there's a significant difference between inner and outer joins, but otherwise it's all about the same.
 
Got Haggis? said:
okay shoddys example works great. However something really strange happens when I add another table. I forgot I had another table, called components, which contains material_id and components. So I figured I could just modify the statement to be
Code: 
SELECT 
    materials.*, languages.*, producers.*, components.*
FROM 
                materials_topics
JOIN          materials ON materials.material_id = materials_topics.material_id
LEFT JOIN   languages ON languages.material_id = materials_topics.material_id
LEFT JOIN   producers ON producers.material_id = materials_topics.material_id
LEFT JOIN   components ON components.material_id = materials_topics.material_id
WHERE 
   materials_topics.topic_id = '$topic_id'

this only returns 1 result, when it shoudl be returning 5. If I take out the reference to the component table stuff, then it returns the correct 5 results, just without component info. Why is it doing this?
Click to expand...

Syntax looks correct. Is the components table a one to many relationship with (not that it should matter as you're doing outer joins, but...) materials_id?
 
a material can have several components (component table is made up of id, material_id, component and URL)
languages is like that too. so now that i think about that, that sql statement really isn't returning the correct results is it
 
Last edited:
SELECT Topics.topic_id, Topics.topic, materials_topics.*, languages.*, Materials.*, producers.*, components.*
FROM Topics INNER JOIN
materials_topics ON Topics.topic_id = materials_topics.topic_id LEFT OUTER JOIN
components ON materials_topics.material_id = components.material_id LEFT OUTER JOIN
languages ON materials_topics.material_id = languages.material_id LEFT OUTER JOIN
producers ON materials_topics.material_id = producers.material_id LEFT OUTER JOIN
Materials ON materials_topics.material_id = Materials.material_id
WHERE (Topics.topic_id = '$topic_id')
 
You must log in or register to reply here.
Back
Top