[SineWaveMaster]

Hi there,

I have a database with roughly 15 tables, 10 of which each have a person_id. I want to bring back all the information from each table that corresponds to a specific person_id. I've found that I can do:

SELECT * FROM table1 LEFT JOIN table2 USING(person_id) LEFT JOIN table3 USING (person_id) LEFT JOIN table3 USING(person_id)......WHERE table1.person_id = X


Is there any shorter way for me to code this without having to continually repeat the 'USING' command over and over? Maybe even avoid using the LEFT JOIN command over and over.....

Any help is greatly appreciate, thanks in advance!

[larkost]

SQL is not known to be a terse language... this is just a part of using it.

[Turias]

A 10-table inner join? Wow. I hope performance doesn't matter in your application.

As for syntax, I'm not sure, but there might be some MySQL-only syntax that might make this easier, but then it wouldn't be compatible with any other database. I would not recommend this in case you ever have to move from MySQL to something else.

[Arkham_c]

Originally posted by SineWaveMaster:
Is there any shorter way for me to code this without having to continually repeat the 'USING' command over and over? Maybe even avoid using the LEFT JOIN command over and over.....

What about something like this:

Code:
SELECT table1.person_id, table2.person_name,table3.foobar, table4.schmoo
    FROM table1, table2, table3, table4
WHERE table1.person_id = 'something'
    AND table1.person_id = table2.person_id
    AND table1.person_id = table3.person_id
    AND table1.person_id = table4.person_id

As a general rule I never use "SELECT * FROM", instead specifying the fields from the tables I want to pull in.

[larkost]

If this were a better database than MySQL, such as PostreSQL then you could create a view to take care of both the hairy syntax and alleviate some of the performance penalty... but...

[Love Calm Quiet]

larkost...

Programming does not come easily to me, so I have just used MySQL for simple db use on my web site (and ease of support on whatever hosting service I'm on).

How is the learning curve to switch to PostreSQL (for a PHP front-end)? the universality of support at Hosts? The additional power to be gained?

[larkost]

As far as switching the SQL... PostreSQL supports more of the SQL standard than MySQL. As far as I know there is nothing that MySQL does that PostreSQL does not do (the reverse is definitely not true). So you can add whatever you want out of the available SQL.

On the admin side, there is the annoyance of having to periodically "vacuum" the db on PostgreSQL, and there are some differences in the security model, but since most people don't even know about the security model on MySQL... The vacuuming is probably the reason that more hosts don't provide PostgreSQL. More recently this has become less of a problem, but people have not caught on yet.