homepage Welcome to WebmasterWorld Guest from 54.167.177.180
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL statement query? is this valid
sql query join, is ths syntax valid
Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
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.

Cheers,

MRb

 

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
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,

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved