[RESOLVED] sql problem

[raj_2006]

Hi all,

I am facing a sql problem....Lets demonstrate it.

I have a product table where 4 records are there.

pid(product id) is the primary key,autoincremental and cid is the category id.

cid pid
20 1
20 2
20 3
20 4

In another table named special there are 3 records.But in this table there is no primary key.here is also the same pid as in product table.

pid discount
1 10
2 20
3 30

Now i want to show the all products either with discount or without discount
so it will be like this

pid discount
1 10
2 20
3 30
4 no discount //this text i can display.

Now I am writing this query


$pr="select * from product where cid='$master_id'";//Here say the master id is 20

$qpr=mysql_query($pr) or die(mysql_error());
while($prw=mysql_fetch_array($qpr))
{
$pid=$prw['pid'];
$msql="select * from special where pid='$pid'";
$mql=mysql_query($msql) or die(mysql_error());
while($mrw=mysql_fetch_array($mql))
{
?>
//product list display
<?}}?>

But doing this only 3 products are displaying.I think i need to give another condition in the 2nd sql......is it?


Please give me your suggestion

Thanks for your co-operation in advance.

Raj

[chazzy]

what version of mysql are you using?

also note that the first query is not needed, you can get everything for a particular cid using joins.

[raj_2006]

Hi

I am using mysql 4.1.14.

ok so if i use only one query then will it be something like this?

$sql="select * from product,special where product.cid='{$_GET['master_id']}' and product.pid=special.pid";

I think in this case it will also display only 3 rows not 4.Please suggest.

Thanks

Raj

[chazzy]

what happens if you use a left join?

SELECT * from product LEFT JOIN special on product.pid = special.pid WHERE product.cid = $masterId;

[raj_2006]

yeah its displaying all the 4 records wether it has any discount or not...


But just from curiosity what will be the sql if i join with a (.) operator.

[chazzy]

join what with a . operator? can you explain?

[raj_2006]

I mean that instead using left join can i do in this way?

SELECT * from product,special WHERE product.cid = $masterId and product.pid = special.pid

but in this way it will return 3 rows....so if i use only (.) then how the sql will look like?

[chazzy]

again, i'm not sure what you mean.

when you do something like that, it is equivalent to

SELECT * from product INNER JOIN special ON product.pid=special.pid WHERE product.cid = $someId;

i'm not sure why you would want to avoid using a left join, it performs much better than the statement you have provided. there is no equivalent in the way you have presented.

[raj_2006]

I have understood what you told......its ok..


Thanks very much

Raj