Accessing complete table data with inner join

[schalkl]

Hi All.

I am stuck at the moment and hope that someone can help.

My problem is as follow:

$sql4 = ('SELECT *,COUNT(*) FROM tt_new_make
inner join tt_new_size
ON tt_new_size.no=tt_new_make.no
inner join tt_new_rub
on tt_new_rub.no=tt_new_make.no
WHERE tt_new_size.s_line_no1="12R" GROUP BY m_line_no1, line_no1');
$sql5 = mysql_query($sql4);

The above results give me the correct answer according to tt_new_size.s_line_no1="12R" which is 2, but I actually need to broaden the querry to access all table fields.

 I have data on another 2 coulombs called s_line_no2 and s_line_no3, which I also need - see attachment. The correct answer should be 4, not 2.

 

 

[Barand]

Normalize you data so you have the values in a single column on multiple rows and your problem goes away. Using db tables like spreadsheets is never a good idea.

[schalkl]

Hi Sen.

Thank you for your quick responce, can you give me an example please?

[schalkl]

Hi Sen

I hear you, but its a bit different situation.

The user is completing a form which might have the R12 on multiple fields.

How do I overcome this problem?

[Barand]

Hi Sen.

 

If you can't work out that my name on my posts is in exactly the same place that your name is on your posts, then I am not sure you are ready for this.

 

Anyway, your table should look like this.

no   |  seq   |  s_line_no
-----+--------+------------
  32 |   1    |    315
  34 |   1    |    12R
  34 |   2    |    12R
  34 |   3    |    12R
  35 |   1    |    12R
  37 |   1    |    Other

If the user enters more than one value then you write more than one row.

 

Now all you need is a count of the rows where s_line_no = 12R to get the answer of 4.

[schalkl]

Hi Barand.

Please forgive me. Call it newbie syndrome ;-)

Ok, I will try that. Thank you for your help. I will keep you updated on the development.

[schalkl]

Hi BarandT

Thank you very much for showing me where I went wrong.

I really appreciate your help. I have completely missed the easy solution.

Thanks again