SQL statement query? is this valid

Forum Moderators: open

Message Too Old, No Replies

SQL statement query? is this valid

sql query join, is ths syntax valid

Matthew1980

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.

Cheers,

MRb

whoisgregg

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

rocknbil

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,

LIMIT 1

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