Query from 2 separate tables?

[ncncnc]

Hi all,

 

I'm currently working on a webpage that dynamically shows tables of sales for products. I'm using SQL to store 2 tables, one containing product info (name, price etc.) and one containing sales for each product.

 

One on of my web pages I want to show the product name and sales for a single year. This would involve ripping the product name from my product table and the sales and year from my sales table. Is this possible in one query?

 

$Query= "select name from productTable and sales, year from salesTable where year = $desiredyear;

 

Obviously I know this is wrong because it isn't working but am I even on the right lines?

 

Thank you.

[thempower]

you need something like this :

 

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT middenoosten2.*, users.naam ".

"FROM middenoosten2, users ".

"WHERE middenoosten2.speler = users.inlog order by id asc";

[ncncnc]

you need something like this :

 

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT middenoosten2.*, users.naam ".

"FROM middenoosten2, users ".

"WHERE middenoosten2.speler = users.inlog order by id asc";

[scootstah]

You need to use JOINs.

 

Post your table layouts and I can help you more. Run this query for each table and post the results:

SHOW CREATE TABLE tablename;

[ncncnc]

Hi

 

Thanks for the reply.

 

I'm unable to access my databases as I am at work, but I can tell you the headers from each table.

 

Sales table: ID, ProductCode, Month, Year, SalesVolume.

 

Product Table: ID, Name, Price.

 

 

Product code in Sales and ID in Product are the same thing.

 

So on my website I want to run a query that shows the name of a product (from product)  and what it has sold in a certain year (from sales).

 

If this information is not sufficient for you, let me know and I will have to wait until I next get the opportunity to access my code.

[scootstah]

I haven't tested this so it may or may not work, give it a go.

SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM Sales WHERE ProductCode=prod_id AND Year='$year') AS num_sales
FROM Products;

 

It should give you a row "num_sales" with the number of sales for each product and the desired year.

[ncncnc]

I haven't tested this so it may or may not work, give it a go.

SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM Sales WHERE ProductCode=prod_id AND Year='$year') AS num_sales
FROM Products;

 

It should give you a row "num_sales" with the number of sales for each product and the desired year.

 

Thanks a lot.

 

I'll give this a go and get back to you.

Is this based on the fact that relationships have been set up in SQL? or will it work regardless?

 

I assume this is the same formula for any combination of things from tables?

[scootstah]

Is this based on the fact that relationships have been set up in SQL? or will it work regardless?

 

It just requires that you have a way to reference both tables. For example your sales.productcode references products.id

 

I assume this is the same formula for any combination of things from tables?

 

Not necessarily. It really depends what kind of data you need. Usually you use JOINs, but in this case you needed a subquery to gather the number of sales for a particular year.

 

If we only used a JOIN in this case, it would result in all the rows for the sales being returned. It would be like,

ID   | Name | Year
------------------
1    | Soda | 2012
NULL | NULL | 2012
NULL | NULL | 2012
NULL | NULL | 2012

 

You'd then have to count the number of sales in PHP which is unnecessary. Instead, with a subquery, we can get just one row per product showing the number of sales. Something like

ID   | Name | Year | num_sales
------------------------------
1    | Soda | 2012 | 4

 

Hope that makes sense.

[ncncnc]

Hello agian,

 

I've had chance to test this code and it doesn't like it.

 

I'm getting this:

 

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\3.php on line 37

 

This must be something to do with the query right?

 

  $describeQuery = "SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM MonthlySales WHERE ProductCode=prod_id AND Year = 1990) AS num_salesFROM Products";

$results = sqlsrv_query($conn, $describeQuery);

echo '<table border="1" BORDERCOLOR=Black>';


while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) 
{
    echo '<tr>';
echo '<td >' .$row['Name'].'</td>'; 
echo '<td>' .$row['ProductCode'].'</td>'; 
echo '<td>' .$row['Year'].'</td>'; 
echo '</tr>';
} 

echo '</table>';
sqlsrv_close($conn); 

 

Any ideas?

[Muddy_Funster]

Why don't you have error capture on your query? change this line to the following and run it again, it will point you to the error

$results = sqlsrv_query($conn, $describeQuery) or die (print_r(sqlsrv_errors()));

[scootstah]

I think it's because you need a space before FROM.

$describeQuery = "SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM MonthlySales WHERE ProductCode=prod_id AND Year = 1990) AS num_sales FROM Products";

[ncncnc]

I think it's because you need a space before FROM.

$describeQuery = "SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM MonthlySales WHERE ProductCode=prod_id AND Year = 1990) AS num_sales FROM Products";

 

Nope, I'm still getting the same error.

 

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\4.php on line 31

 

I have a feeling it's something simple like the wrong type of brackets or something, but I've tried a few things and it doesn't like any of them.

[scootstah]

After you run the query, do something like

if (sqlsrv_errors() !== null) {
    print_r(sqlsrv_errors());
}

[ncncnc]

Array ( [0] => Array ( [0] => 42S22 [sqlSTATE] => 42S22 [1] => 207 => 207 [2] => [Microsoft]

[sql Server]Invalid column name 'prod_id'. [message] => [Microsoft][sql Server Native Client 10.0][sql Server]Invalid column name 'prod_id'. ) ) 

 

[scootstah]

My knowledge on SQL Server syntax is limited but I think it may be an issue with the alias. Try this:

$describeQuery = "SELECT ID, Name, (SELECT COUNT(*) FROM MonthlySales WHERE ProductCode=Products.ID AND Year = 1990) AS num_sales FROM Products";

[ncncnc]

Thanks a lot, It's working now.