Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    May 2006
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post

    querying form multiple tables

    I'm trying to get info from multiple tables, and not having much success. I want it to be sorted by date, as if they were 1 table. Therein lies (one of) the problem, one is just date(YYYY-MM-DD) and the other is date and time(YYYY-MM-DD HH:MM:SS). Also, I'm not sure that everything else is right, or anywhere near it.
    PHP Code:
    select 
    from `comics`, 
    `
    news home
    where comics.date <= '".date('Y-m-d')."' 
    order by comics.date
    `
    news home`.date 
    desc 
    Last edited by guelphdad; 02-15-2007 at 06:13 AM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you are not joining your tables on any columns, therefore you will get cross join effects matching every row in the one table with every row in the other table. a 40 row table and a 30 row table combine for 1,200 rows!

    also don't bother with PHP in your mysql query, use what you need in your query.

    Have a read on the main site I've linked below so you understand joins.

    as for joining a date column and a datetime column you can format as follows:

    Code:
    where datecolumn = DATE_FORMAT(datetimecolumn,'%Y-%m-%d')

  • #3
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    ah, i thought spaces in table column names was a bad practise.

    if since there is no info on tbe table columns, i'm just guessing, but you'll need to join them like guelphdad said.

    something like this, also note that you can use short table names.
    PHP Code:
    SELECT FROM 
    comics c
    news home nh
    WHERE c
    .id nh.ComicsID AND c.date <= '".date('Y-m-d')."' 
    ORDER BY c.date
    nh.date 
    DESC 
    Last edited by guelphdad; 02-15-2007 at 07:08 PM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I would always use an explicit ON condition in the join:

    Code:
    SELECT * 
    FROM
    comics c
    INNER JOIN
    news home nh
    ON c.id = nh.ComicsID
    list join syntax as posted above is too easy to make mistakes with. also when you include an OUTER JOIN with list join syntax, it is easy to make an error that mysql5 will spit back at you.

    Normally I've seen those posted with a "but it worked on 4.x" plea!

  • #5
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    no, thats not exactly right. though it seems to works regardless.

    Inner Join should only be used to join a table to itself.
    Outer Join is the explicit option for joining different tables.

    "INNER join SQL command is mostly used to join one table to it self. The biggest advantage of doing this is to get linking information from the same table"

    this can all be found in the mysql manual.

  • #6
    New Coder
    Join Date
    May 2006
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post
    I think I must have said something wrong. This is for an ATOM feed, for a comic site, it would combine the comics and the news tables (as different rows) into the same feed. What I want to end up with is something like:
    Row From A
    Row From A

    Row From B
    Row From A
    Row From A
    Row From A

    Row From B
    Row From B


    sorted by date. I would use an if statement to determine which table it is from by testing to see if one of the variables is set.
    Also the PHP date is to get the current date, I wasn't aware there was a way to do this through mySQL

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The way your syntax is worded, you are doing a join and as Guelphdad mentioned, that will have the effect of multiplying the results of each table together.

    What you really want is a UNION, bringing the two tables together so the results come out as if they were from one single table. Get rid of the wildcard select and use alias names...

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by angst View Post
    Inner Join should only be used to join a table to itself.
    Outer Join is the explicit option for joining different tables.
    That is absolutly incorrect.

    An inner or outer join can be used to join a table to itself.

    An inner join is used between two tables when you only want matching rows from the two tables.

    An outer join is used between two tables when you want all the rows from one table even if there are no matching rows in the other table.

    Imagine two tables one with student id and student name the second with student id and test scores.

    Use an INNER JOIN either like this:

    Code:
    select
    student.id, student.name, test.score
    from
    student
    inner join test
    on
    student.id = test.student.id
    you would use that when you only wanted students with test scores and not sutdents who have no scores.

    This is exactly the same query as the following which is ALSO an INNER JOIN:

    Code:
    select
    student.id, student.name, test.score
    from
    student, test
    where
    student.id = test.student.id
    which is just written poorly.

    When you want a list of students and test scores INCLUDING those students who have not participated in the test yet then you would use an OUTER JOIN.

    Code:
    select
    student.id, student.name, test.score
    from
    student
    left outer join test
    on
    student.id = test.student.id

    list syntax with a where clause is still an inner join, it is sloppy syntax and much easier to make a mistake using it. Unfortunately it is very common in the mysql manual to see this type of join used and gets people into bad habits.

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Unfortunately it is very common in the mysql manual to see this type of join used and gets people into bad habits.
    It's the only syntax some of us could use for years in other SQL databases

    I'd say it's a matter of preference, not a sloppy vs. non-sloppy debate.

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    with a five table join in list syntax and two of them left outer joins can you tell me which join condition belongs with which table? of course, but it isn't as simple to see. if you have a where condition on the right hand table of a left join then you have reduced your left outer join to an inner one.

    if you list the three tables in list syntax first and then add the two outer joins, mysql 5 will spit out an error if the tables are not joined in exactly the right order.

    one isn't wrong over the other, but I'd argue that the explicit ON syntax is clearer.

    The info on the inner/outer joins stated above though is incorrect so I pointed that info out.

  • #11
    New Coder
    Join Date
    May 2006
    Posts
    34
    Thanks
    2
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Fumigator View Post
    What you really want is a UNION, bringing the two tables together so the results come out as if they were from one single table.
    It seems that won't work either. The two tables have completely different information in them, and a different number of columns.

  • #12
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I'm assuming the info from the two tables is going to come together at some point though, right? And as the data from the two tables comes together, you'll have to resolve the differences at some point, so why not at the point you select the data? You can specify the fields to select from each table (don't use SELECT *) and use alias names.

    Or, just run two queries and sort the results using an array sort function in your server script.

  • #13
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Guelphdad-- I absolutely agree with you that one should use explicit JOIN ON syntax when dealing with anything but a normal straight-forward inner join.

  • #14
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I used to use list syntax, until I realized it was easier to make mistakes and muddle up the more complicated ones so now I always stick to them. ;-)

  • #15
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    your really arguing method here, and nothing more.

    all versions of mysql that i've ever used in the past 8-9 years including the newest release work fine without the explicit join.

    I agree that for more complex queries it is better to use a join method ,right, inner, left, outer, and so on.

    but for a simple query like this, it doesn't matter. there will be no errors, and it will run fine.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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