SQL gurus....

[sn00p]

Wonder if anybody knows the answer to this...

Say I have two tables "customers" & "orders"

both tables have an "id" field to uniquely identify them. Orders has a field called "customer_id" to link up to the customers table.

I currently run a query like:

SELECT customers.*, orders.* WHERE customers.id=orders.customer_id;

which gives me a nice result table.

However, because both tables contain the field "id" - I get one or the other.

How do I get around this? I remember there being an AS clause, but that would mean adding each field from the table to the select individually.

Can you get it to name the parts of the result set? (i.e so access is like "customers.id" "orders.id")

I could do multiple queries, but I'm sure you must be able to do what I want with a single query.

This is a php website running with a mysql db that i'm working on.

Thanks.

 

[cava83]

should be able to.

Let me dig my old sql notes

 

[172.com]

How do I get around this? I remember there being an AS clause, but that would mean adding each field from the table to the select individually.

You should be doing that anyway. It's inefficient otherwise.

 

[cava83]

can u post our exact input query and results too.

 

[sn00p]

How do I get around this? I remember there being an AS clause, but that would mean adding each field from the table to the select individually.

You should be doing that anyway. It's inefficient otherwise.

I need all the fields from the "merged result" barring the one of the duplicate id fields.

I can get around this by simple reordering the order in the select statement.

 

[172.com]

You should always name the fields for output, even if it's all the fields you want, plain and simple. However, if you do wish to be lazy, you say that it outputs one of the id fields. If it's the same field everytime e.g. the customers id then use SELECT customers.*, orders.*, orders.id AS OrderID

You might also want to use an Inner Join on that select rather than a where. e.g
SELECT customers.*, orders.*, orders.id AS OrderID
FROM Customers INNER JOIN
Orders ON customers.id = orders.customer_id

Same results, but ANSI compliant and better coding practice imo.

 

[cava83]

Geek but nicely put

You should always name the fields for output, even if it's all the fields you want, plain and simple. However, if you do wish to be lazy, you say that it outputs one of the id fields. If it's the same field everytime e.g. the customers id then use SELECT customers.*, orders.*, orders.id AS OrderID

You might also want to use an Inner Join on that select rather than a where. e.g
SELECT customers.*, orders.*, orders.id AS OrderID
FROM Customers INNER JOIN
Orders ON customers.id = orders.customer_id

Same results, but ANSI compliant and better coding practice imo.