Hi every/anyone.
I was wondering if anyone could help me with a MySQL problem that I'm trying to figure out.
I'm working with 5 tables. The Sales, , Salesdetail, Product, Contacts and Users tables.
In the users table there is a field labelled referer. It is the referer the user came from (ie google search) that lead to the sale.
Generally I can fetch the referer from the users table with
users.contact_id = contacts.contact_id
But what I am trying to get is a list of referers that lead to a sale with a specific product number (products table)...
Below is the basics of each table.
Code:
mysql> describe product;
+------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| product_no | smallint(5) unsigned | | PRI | 0 | |
|
+------------------+----------------------+------+-----+---------+-------+
----
mysql> describe users;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| user_id | int(10) unsigned | | PRI | NULL | auto_increment |
| referer | varchar(255) | YES | | NULL |
| contact_id | int(10) unsigned | | | 0 | |
+------------+------------------+------+-----+---------+----------------+
----
mysql> describe sales;
+--------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+----------------+
| sales_id | int(10) unsigned | | PRI | NULL | auto_increment |
| contact_id | int(10) unsigned | | MUL | 0 | |
| sale_date | datetime | YES | | NULL | |
+--------------------+---------------------+------+-----+---------+----------------+
----
mysql> describe salesdetail
-> ;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+----------------+
| salesdetail_id | int(10) unsigned | | PRI | NULL | auto_increment |
| sales_id | int(10) unsigned | YES | MUL | NULL | |
| product_no | smallint(5) unsigned | YES | MUL | NULL | |
| size | varchar(50) | | | | |
| time_updated | datetime | YES | | NULL | |
+----------------+----------------------+------+-----+---------+----------------+
----
mysql> describe contacts;
+--------------+---------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------------------------+------+-----+---------+----------------+
| contact_id | int(10) unsigned | | PRI | NULL | auto_increment |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+--------------+---------------------------------------+------+-----+---------+----------------+
---
So basically it's pretty easy to get the
one referer from a product that was sold but is it possible to get all the
referers from one product and all its sales?
I hope I haven't confused anyone. I'm not the greatest with MySQL but I'm getting there...Any help is appreciated.
Thanks everyone.