table join SQL question

[Anon]

Say I have this SQL that joins two tables. When I print out the table those fields that are matching in the join query are printing out twice. Is their a way to only get them to print once since twice is redundant?

$result3 = mysql_query( "SELECT * FROM test LEFT JOIN login ON test.id = login.id WHERE test.registration = '49878sd'" );

[[deleted]]

You get what you ask for: a list of all the fields in both tables.
If you don't want that, you'd have to specify which fields you do want:

SELECT fields, that, I, want FROM table1, table2 etc...

[Anon]

Or, you use the word DISTINCT, like:

$result3 = mysql_query( "SELECT DISTINCT * FROM test LEFT JOIN login ON test.id = login.id WHERE test.registration = '49878sd'" );

[fleymingmasc]

SELECT test.* FROM test
LEFT JOIN login ON test.id = login.id
WHERE test.registration = '49878sd'

Note that * will retrieve fields from all joined tables unless you limit it to a specific table.

[fleymingmasc]

It is considered a "best practices" sort of thing not to allow SELECT * in production SQL code. It is better to specify exactly which columns you need. The fewer columns you ask for, the less overhead you incur. This becomes an issue when optimizing a high-volume site.

This is also helpful when debugging... if you request a column that was changed or deleted using an ALTER TABLE, you'll get a much more meaningful message back from your SQL server if you requested that field by name.

The exception to this rule is when you SELECT COUNT(). You may get deceptive results if you try to specify a column instead of the , because NULL values in that column won't be (or shouldn't be) counted. (If I remember correctly from the SQL standard.) When you SELECT COUNT(*) you are guaranteed to get the number of rows in the table, null or no.

[[deleted]]

Distinct makes the ROWS unique, not the COLUMNs.

[[deleted]]

Note that allthough all of this is true, you don't have to immediately start worrying about loss of performance because of a SELECT * statement.

Yes it does give more overhead and yes it does use more memory, but unless you are running a very busy site or you are dealing with large BLOB objects, using a SELECT * will not give any noticable performance drop, while it may give you some advantage lateron, especially if you are working in a modular/OOP fashion.

[Anon]

It is good practice though to SELECT the fields that u need. 🙂 Do it right the 1st time and everytime, less worries later on. 🙂

[[deleted]]

It's debatable. :-)

[Anon]

There are [Generalistics] and [Specifics] 🙂 I like to be clear what I want, like selecting fields BUT if I'm gonna use like over half the fields in the tables, I'll heck it and go with [*]. 😉 LOL