SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Arrow Joining table with out 'join'?

    Just for curiosity i had tried creating tables and combining their contents
    without the use of JOIN command.

    here are the 3 tables i created.
    CREATE TABLE person
    (
    personID SMALLINT NOT NULL PRIMARY KEY,
    fname VARCHAR(60) NOT NULL
    )
    ENGINE=INNODB;

    INSERT INTO person
    VALUES (1001, 'arthur'),
    (1002, 'joel'),
    (1003, 'william'),
    (1004, 'warren'),
    (1005, 'rachel'),
    (1006, 'james'),
    (1007, 'richard'),
    (1008, 'jerome'),
    (1009, 'michael');


    CREATE TABLE place
    (
    placeID SMALLINT NOT NULL PRIMARY KEY,
    place VARCHAR(60) NOT NULL
    )
    ENGINE=INNODB;

    INSERT INTO place
    VALUES (2001, 'new york'),
    (2002, 'boston'),
    (2003, 'virginia'),
    (2004, 'california'),
    (2005, 'washington'),
    (2006, 'nevada'),
    (2007, 'new mexico'),
    (2008, 'ohio'),
    (2009, 'las vegas');


    CREATE TABLE position
    (
    positionID SMALLINT NOT NULL PRIMARY KEY,
    position VARCHAR(60) NOT NULL
    )
    ENGINE=INNODB;

    INSERT INTO position
    VALUES (3001, 'janitor'),
    (3002, 'crew'),
    (3003, 'police'),
    (3004, 'supervisor'),
    (3005, 'manager'),
    (3006, 'maid'),
    (3007, 'healer'),
    (3008, 'doctor'),
    (3009, 'programmer');
    now this is the SELECT.
    SELECT fname, place, position FROM person AS pe, place AS pl, position AS po WHERE pe.personID=1006 AND pl.placeID=2005 AND po.positionID=2007

    but i don't see the result, here take a look the link below.
    http://czone01.com/zphp/nojoin.php

    the page is all white?

    i wonder, where is my mistake?

    thank you.

  2. #2
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    no need to reply i already solve my problem

    i just change it to this.

    $query = "SELECT fname, place, position FROM person AS pe, place AS pl, position AS po WHERE pe.personID=1006 AND pl.placeID=2005 AND po.positionID=3007";

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that SQL statement doesn't do what you think it does. if you ever write a query like that and one of your tables has multiple rows matching your condition then you'll get results you don't expect.

    note to anyone else reading this post: don't write your queries like this.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Evangelist
    Join Date
    Nov 2002
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And keep in mind that JOINs are optimized for speed as well...

  5. #5
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Arrow

    Quote Originally Posted by longneck View Post
    that SQL statement doesn't do what you think it does. if you ever write a query like that and one of your tables has multiple rows matching your condition then you'll get results you don't expect.

    note to anyone else reading this post: don't write your queries like this.
    longneck my friend i know you are also a guru of SQL

    but don't worry it's for testing purpose only.

    I know how to use JOIN.

    cheers


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •