getting data from two tables into a report 1

[sxmnl]

Hi, I'm sure this is easy for someone.. I'm trying to create a report for a affiliate from my web site. He would like a monthly report to show what products he sold from his site.
I have two tables that this data resides on.
1) tblorderdetails
2) tblorders

in the tblorders there is a field AffiliateID, and OrderID
OrderID is the coralating field between the two tables

I tried this

select orderID, productname
from tblorderdetails where AffiliateID in (
select distinct OrderID from tblorders
where AffiliateID = '4'
)
Like I said the orderID is the same in both tables except that the AffiliateID resides in the tbleorders and pruductid resides on the other.

If you can Help I would Appreciate it.

Thanks in Advance
I'm using mysql and SQLyog as the front end.

Thanks
lee

 

[checkai]

Select x.orderid, x.productname
from tblorder y
inner join tblorderdetails x on y.orderid = x.orderid
where y.affiliateid = 4

dlc

 

[sxmnl]

Thanks,

I have a question. This will not join the tables will it ? I really just want to preform this query once a month with out effecting the tables. If I can get the query correct then I can do the same with customers and what product they purchased.

Also I'm new to the website sql can you explain the x, and y,

Thanks Again
Lee

 

[checkai]

This JOINS the tables for this query...doesn't affect the table at all...simply for querying the data.... the x and y are called aliases...so the query knows which table to take the field from...the same as doing this...

Select tblorderdetails.orderid, tblorderdetails.productname
from tblorder
inner join tblorderdetails on tblorder.orderid = tblorderdetails.orderid
where tblorder.affiliateid = 4

dlc

 

[SQLBill]

BTW- if you have problems with the script provided by checkai, it most likely is because you aren't using Microsoft SQL Server (which this forum is for).

We can probably help you with basic SQL syntax, but all SQL is not created equal. MySQL has it's own pecularities and not all Microsoft SQL Server's TSQL will work with MySQL.

You might consider posting in the MySQL forum.

-SQLBill


SELECT 'forum' CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'