Possible MySQL Doozie...

[jtracking]

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.

 
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.

[jtracking]

OK So I've tried many SQL statements to no avail but the one below 'seems' to be the closest to what I need but I'm still getting an error...

select referer, contact_id   
from users where users.contact_id in
(select sales_id, contact_id from sales, salesdetail where
sales.sales_id = salesdetail.sales_id
and salesdetail.product_no = #pnumber#);

The error states: Column 'sales_id' in field list is ambiguous

Bare with me and if you have any idears, feel free to let me know them...

P.S. #pnumber# would be product number.

Thanks.

[jtracking]

Hm, I even tried a join statement and that didn't work...

this is quite gruelling...

 
 select users.referer, contact_id, sales_id, product_no,contact_id from sales, salesdetail   
 from users, sales, salesdetail join salesdetail on sales.sales_id = salesdetail.sales_id
   and salesdetail.product_no = #pnumber#;

[jtracking]

Ok im going to try to select my info in a backwards motion, meaning im going to select the products from the salesdetail table where the product_no = #pnumber# and see if I can get the referal from that direction...

I'll be back...

[wige]

Just to restate what I think you are trying to accomplish: You want a list of all of the refferrers for all sales of a specific product by product ID?

Reading through the table, though, it looks like you have a 1-1 relationship between the users table and the contacts table, and it might be more effective to merge the tables. Then the sales table would have a reference to the user_id instead of the contact_id. As it stands now, I think you need to

SELECT user.referer FROM salesdetail LEFT JOIN sales ON salesdetail.sales_id=sales.sales_id LEFT JOIN contacts ON sales.contact_id=contacts.contact_id LEFT JOIN users ON contacts.contact_id=users.contact_id WHERE salesdetail.product_no = 'YOURPRODUCTNO' GROUP BY user.referer

Note that A. I have not tried this, and B. Group By will remove duplicates, so you won't see how many times that referrer resulted in a sale.

[jtracking]

Well thank you Senior...I'm going to look into what you just posted...I'm rather confused with the left, outer, right and full joins...but I'm getting there...

I'll definitely keep you posted...

[computergenius]

Originally Posted by jtracking

The error states: Column 'sales_id' in field list is ambiguous

MySQL doesn't know which table you are referring to, because sales_id is obviously in more than one table.

You need to change it to SELECT thistable.sales_id - replacing thistable with the relevant tablename - you have done this in other places in your SQL.

[jtracking]

Yes I agree...

I've gotten a tad farther with my work and think I will be able to narrow down the referer by starting with the following queries;

<cfquery name="users" datasource="#request.dsn#">
select distinct sales_id from salesdetail where product_no = #pnumber#;
</cfquery>

<cfquery name="users2" datasource="#request.dsn#">
select contact_id from sales where sales.sales_id = #users.sales_id#;
</cfquery>

But for some reason when I try to output data from query users2 nothing is displaying but I'm SURE there's data there...

thanks again for your support CG, I'll get this surely in time...

[jtracking]

...shoot, i have to get the data from an array in the second query...

[jtracking]

Sweet. *sighs*

OK this wasn't so much of a doozie and I have to thank everyone for their assistance.

I got it to work and I did by going backwards instead of forwards, in other words,

instead of starting off by selecting the referer based on contact_id and sales_id stuff,

I first selected the product number from the salesdetail table,

then the sales_id from the same table,

then the contact_id from the sales table based on the contact_id in the salesdetail.sales_id

and then once I had that contact_id i fetched the referer from the users table...

the sql is all mixed in with coldfusion but if you need to see it just let me know and I will post it here...

Thanks everyone...

[jtracking]

OK so I posted the resulting code and queries I used to make the script work here: Help me clean my ColdFusion Script.

I wanted to see if there were any Master CF developers who could help me clean my script up a little bit...