Msg#: 4075723 posted 10:15 pm on Jan 28, 2010 (gmt 0)
Hi there people of webmaster world,
I'm completely new to the world of joins in mysql and I would like to know if this syntax *hypothetically* would be correct:-
SELECT `column_name1`, `column_name2` FROM `table_name1` FULL JOIN `table_name2` ON `table_name1.column_name1`, `column_name2=table_name2.column_name` WHERE `column_name` = 'cell_data' LIMIT 1
The idea is that table_name1 has 1 column in common with table_name2 and table_name2 has 5 possible pieces of data that will match EXACTLY with 1 piece of data in table_name1, (at this stage table_name2 has only 1 column) thus result will return only one row, I think.
Could anyone advise me as to whether I am indeed in the right area with this, again it hypothetical at this point, but will be used once I have the theory nailed.
Msg#: 4075723 posted 10:22 pm on Mar 23, 2010 (gmt 0)
Hypothetical queries are the toughest to sort out. Many join queries will produce some kind of result, it's just a matter of whether that result is what you want it to be. I find it's typically helpful to run queries against real data to ensure you're getting the right result. (And, if you have large data sets, to have a separate test machine in case a query is particularly wrong.)
One thing that does jump out at me is the ambigious `column_name`='cell_data'... I'd change that to be `table_name1`.`column_name` (or whatever table it should reference).
Msg#: 4075723 posted 2:41 am on Mar 24, 2010 (gmt 0)
Here's a couple more. First might just be a "doh" moment:
will match EXACTLY with 1 piece of data
If it matches with 6 million pieces,
insures it will only output one. :-)
The second question is other than foreign keys joining the two, why would you ever have a relational table with the same bits of data in both tables (on purpose)? The whole idea of normalizing a database using relational tables is so you don't have redundant data taking up space.
Last, I may be incorrect, don't think that this is valid . . .
. . . FULL JOIN table_name2 ON table_name1.column_name1, column_name2=table_name2.column_name . . .
the comma is for selecting fields, etc . . . in the join clause I think you want
. . . FULL JOIN table_name2 ON table_name1.column_name1=table_name2.column_name . . .