CODEWITHSUNDEEP
mysqlsql
Francesco Belladonna
Francesco Belladonna

Reputation: 11689

Two very similar queries that returns a different result, why?

I'm going mad with this 2 queries, I can't explain myself why one returns 81 rows and the other 0. Can someone help me with this?

-- Working
SELECT * FROM
boat_standard_specification_items AS BoatStandardSpecificationItem JOIN standard_specification_items AS StandardSpecificationItem
ON(BoatStandardSpecificationItem.standard_specification_item_id = StandardSpecificationItem .id)

-- Not working
SELECT * FROM
`boat_standard_specification_items` AS `BoatStandardSpecificationItem` JOIN `standard_specification_items` AS `StandardSpecificationItem`
ON (`BoatStandardSpecificationItem`.`standard_specification_item_id` = 'StandardSpecificationItem.id')

I'm on Windows OS if can be helpful in any way

Upvotes: 1

Views: 71

Answers (5)

Sunil Kumar B M
Sunil Kumar B M

Reputation: 2795

In the second query

`BoatStandardSpecificationItem`.`standard_specification_item_id` = 'StandardSpecificationItem.id'

you are comparing BoatStandardSpecificationItem.standard_specification_item_id with a string 'StandardSpecificationItem.id'

In the first query

SELECT * FROM
boat_standard_specification_items AS BoatStandardSpecificationItem JOIN standard_specification_items AS StandardSpecificationItem
ON(BoatStandardSpecificationItem.standard_specification_item_id = StandardSpecificationItem .id)

you are comparing BoatStandardSpecificationItem.standard_specification_item_id with another column StandardSpecificationItem .id

It is so obvious they return different results

Upvotes: 1

Balaswamy Vaddeman
Balaswamy Vaddeman

Reputation: 8530

They are different queries .In second you are trying to match field to a String which you might not want.

(`BoatStandardSpecificationItem`.`standard_specification_item_id` = 'StandardSpecificationItem.id')

you may learn more about Join

Upvotes: 0

f2lollpll
f2lollpll

Reputation: 1007

You can't put the table names inside single quotes. Use [ and ] if you want to put something around them in order to make it readable.

Upvotes: 1

Aaron
Aaron

Reputation: 57748

It looks like the one that's "not working" is using a literal string to JOIN on (note the single quotes). The rest of your query is using back-ticks, so try that once.

Upvotes: 0

Marc B
Marc B

Reputation: 360632

They're not the same query. On the second one, you have:

ON (`BoatStandardSpecificationItem`.`standard_specification_item_id` = 'StandardSpecificationItem.id')
                                                                       ^---

Note that change in quotes. Single quotes define a STRING in a query, so you're not comparing two fields, you're comparing a field against a string.

Upvotes: 3

Related Questions