CODEWITHSUNDEEP
mysqljoin
zealisreal
zealisreal

Reputation: 505

MySQL Left Join help

Hello again community,

I posted a question just over an hour ago asking for some help fixing a SQL query, that was promptly answered and was a great help. Sadly though, upon reflection and further usage, it turns out that the query was returning all rows in the targeted table. I validated this by putting the mysql_fetch_object inside a while loop.

So what I desperately need now is someone to have a look at the SQL query and tell me what is going wrong and if possible how to fix it. I simply just need one row (the correct row) to be found when using $_SESSION['model'] as the reference.

Below is the full content of my previous question.

Thank you in advance.

============================================================================

web_quote_models table

id | model       | product_id | cpu_id | ram_id | hdd_id | os_id | opt_id
=========================================================================
1  | 000001      | 1          | 1      | 1      | 1      | 1     | 1
2  | 000002      | 1          | 2      | 2      | 2      | 2     | 2
3  | 000003      | 1          | 3      | 3      | 3      | 3     | 3
4  | 000004      | 1          | 4      | 4      | 4      | 4     | 4

web_quote_component_cpu table

id | name
==========================================================================
1  | Intel® Core™ i3 2100 3.1GHz dual-core
2  | Intel® Core™ i5 2500 2.7GHz quad-core
3  | Intel® Core%trade; i7 2600 3.4GHz 8mb Cache dual-core

So what I need to achieve is a query that will look inside the web_quote_models table and match the model field with a $_SESSION['model'] then match the web_quote_models.cpu_id field with the web_quote_component.id.

This is what I have so far; I cant be too far off I think.

("
 SELECT web_quote_component_cpu.name
 FROM web_quote_component_cpu
 LEFT JOIN web_quote_models
 ON web_quote_component_cpu.id=web_quote_models.cpu_id
 AND web_quote_models.name='".$_SESSION['model']."'
");

A massive thank you in advance to anyone that helps.

Dan.

Upvotes: 0

Views: 131

Answers (3)

jbrond
jbrond

Reputation: 727

Try this:

SELECT web_quote_component_cpu.name
   FROM web_quote_component_cpu
   LEFT JOIN web_quote_models ON web_quote_component_cpu.id=web_quote_models.cpu_id
   WHERE web_quote_models.name='".$_SESSION['model']."' LIMIT 1

Upvotes: 0

Shahbaz
Shahbaz

Reputation: 47493

With Left Join, you get entries from the table on the left side of join (that is web_quote_component_cpu) even if they are not matched with the other table. If you want entries that only match, use join instead of left join.

Is that what you were asking?

Upvotes: 1

Nicholas Smith
Nicholas Smith

Reputation: 11754

SELECT web_quote_component_cpu.name FROM web_quote_component_cpu JOIN web_quote_models ON web_quote_component_cpu.id = web_quote_models.cpu_id WHERE web_quote_models.model = $_SESSION['model]

(or something like that)

Upvotes: 1

Related Questions