Select all and some

[ynotlim]

Hello,

I have two tables, tracking_info & customer_info

I want to do a select statement for all of the tracking_info table and only the name from the customer info table.

here's what i want to do but it still selects all from both tables, i only want to select all from tracking_info.


SELECT *, name FROM tracking_info, customer_info
WHERE tr_customer_id=customer_id and active=1


Thanks in advance,

Tony

[guelphdad]

Then don't use SELECT *, only name the columns you want.

[Daemonspyre]

While Guelphdad is correct, you only want to pull specific fields, if you must do a server-intensive, memory overhead expensive, non-index using FULL TABLE SCAN, then you can also use:


SELECT ti.*, ci.name FROM tracking_info ti JOIN customer_info ci ON ti.tr_customer_id=ci.customer_id WHERE active=1

[daemonkin]

How about:


SELECT t.*,c.name
FROM tracking_info t, customer_info c
WHERE t.customer_id = c.customer_id
AND t.active = 1


D.

[Daemonspyre]

Same JOIN syntax, just one is more database-independent (i.e., comma joins are not supported past MySQL 4.x).

[ynotlim]

thanks! great advice. The reason I didn't want to name all the fields is because it takes too much time. If there are 20 fields, and I'm only selecting 1 field from the other DB, that takes up too much space and is inefficient.

[mwookie]

If you want to get a good idea about the efficienys of your queries try using EXPLAIN in a commandline client or the Windows GUI client. Give you a good idea on how MySQL is handling it as far as indexes, join orders etc.


_________________________
"Insanity is hereditary - you get it from your children." Sam Levenson
Web Development Company (http://www.emblemsoftware.com) – Projects (Find $1 Images - Dreamstime, StockXpert & More (http://www.imagetrail.net)– Learn how to sell your photos (http://www.microstockforum.com/forums))

[guelphdad]

Same JOIN syntax, just one is more database-independent (i.e., comma joins are not supported past MySQL 4.x).

actually they are. I may have posted here in the past to the contrary because I had read that. the difference is how the comma syntax is ordered. before mysql used to allow sloppy syntax now it doesn't.

As an example



select
foo,
bar,
qux
from tablea, tableb
left outer join tablec
on tablea.somecolumn=tablec.somecolumn
and tablea.differentcolumn=tableb.moescolumn


what mysql did before was look at the columns and try to figure out what you are trying to do. now the syntax conforms more closely to the sql standard.

in the case above, at the point the left outer join and table c are mentioned, only tables B and C are in scope, thus you can't join on column a at that time.

you would have to rewrite the query as:




select
foo,
bar,
qux
from tablea, tableb
where tablea.differentcolumn=tableb.moescolumn
left outer join tablec
on tablea.somecolumn=tablec.somecolumn



but of course the BEST syntax is to eliminate the comma entirely as so:




select
foo,
bar,
qux
from tablea
INNER JOIN tableb
ON tablea.differentcolumn=tableb.moescolumn
left outer join tablec
on tablea.somecolumn=tablec.somecolumn