...

View Full Version : querying form multiple tables



Big-Pat
02-15-2007, 04:48 AM
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.

select *
from `comics`,
`news home`
where comics.date <= '".date('Y-m-d')."'
order by comics.date,
`news home`.date
desc

guelphdad
02-15-2007, 06:19 AM
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:


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

angst
02-15-2007, 04:39 PM
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.


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

guelphdad
02-15-2007, 07:10 PM
I would always use an explicit ON condition in the join:



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!

angst
02-15-2007, 07:33 PM
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.

Big-Pat
02-15-2007, 09:59 PM
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

Fumigator
02-15-2007, 10:19 PM
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...

guelphdad
02-16-2007, 12:20 AM
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:



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:



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.



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.

Fumigator
02-16-2007, 12:34 AM
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 :D

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

guelphdad
02-16-2007, 02:20 AM
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.

Big-Pat
02-16-2007, 03:26 AM
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.

Fumigator
02-16-2007, 06:08 AM
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.

Fumigator
02-16-2007, 07:14 PM
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.

guelphdad
02-16-2007, 08:21 PM
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. ;-)

angst
02-16-2007, 08:28 PM
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.

guelphdad
02-17-2007, 12:22 AM
Yes it is only a matter of syntax, but as you see getting queries right and using the right types of queries is important. Getting into the habit of using ON clauses will get a user into better habits for more complex queries.

Above you stated that INNER joins are used for SELF JOINS only, so you can see how easy it is for incorrect information to propagate.

angst
02-17-2007, 12:29 AM
Yes, thats right out of the MySQL manual. look for yourself.

Fumigator
02-17-2007, 12:33 AM
I actually did look but didn't find anything of the sort. Provide a link perhaps?

guelphdad
02-17-2007, 02:31 AM
Yes, thats right out of the MySQL manual. look for yourself.

Even if it is in the manual it is incorrect information, but yes an actual link would be nice since I've searched and don't see it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum