SELECT query from 2 tables!!!!!

[j-dearden]

Hey

im having a little trouble with this query......

$sql2 = "SELECT sales_table.customer_id, customer_table.*
FROM sales_table, customer_table
WHERE sales_table.customer_id = customer_table.customer_id";

$rs2 = mysql_query( $sql2, $conn );

the idea is i have 2 tables

1. customer table
2. sales table

when the client enters informstion regarding a sale they eneter the id for the cudtomer who made the sale, in turn realting to the customer from the customer table

fopr exam[ple

i have a sale and the id = 1

in the customer table the customer with an id of 1 is say john.

i want to cerate a query that selects everything from the customer table where the customer id in the sales table is equal to the customer id in the customer table if u get my drift. the only problem is at the mo its selecting all the customer id's from the sale table and displaying the information from the customer table depending on how many entries are in the sales table. so say john has made 3 sales in the sales table the id 1 will be there 3 times, so when i run the query johns details are displayed 3 times etc. i hope this is clear enough for you to understand. any help would be most appriciated cheers

john. 🙂

[Sayian]

Joins my good man.

$sql2 = "SELECT a.customer_id, b.*
FROM sales_table a
JOIN customer_table b ON a.customer_id = b.customer_id";

http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html

[j-dearden]

Cheers for that, but its still selects all the ids from sales table and displays the information for each, at the moment there are 3 entries (testing purposes) id, 1 is there 2 times and id, 5 is there once, so its displaying information for id = 1 twice and information for id = 5 once.

by the way customer_id in the sales table is a foreign key

cheers

[Sayian]

Can you run a DESCRIBE on those two tables and paste the results here?

Trying to construct querys without a table schema infront of me is rather difficult.

I can help you, but I need to know the exact design of the tables, in a relational way.

Run a DESCRIBE for me if you have access to SSH

BASH: mysql

BASH: use table_name

BASH: Describe table_name

Cheers.

[j-dearden]

I dont have access to SSH at this time is there a way of running DESCRIBE using php and displaying it on screen cheers

[j-dearden]

Hey i have attached some screen shots for the schema of the 2 tables hope this helps thanks

john

[Lars_Berg]

select <columnsOfInterest>
FROM customer_table
WHERE exists
 (select *
    from sales_table
  where sales_table.customer_id = customer_table.customer_id)