Here is a challenge...maybe

[fry2010]

My in-experience in using join statements has come-a-cropper for me, and Im not even sure if that is what is required here.

 

Here is the situation:

 

I have two tables.

Table two has a column with a foreign key to table one.

I want to select certain column data from table one, but at the same time count(*) how many rows in table two are linked to table one.

 

I know this can be done easy using two statments seperatly, but I imagine there is a solution using just one query.

Here are the two table examples:

 

create table table_one (
id int unsigned not null AUTO_INCREMENT,
name char(60) not null DEFAULT '',
status tinyint(1) unsigned not null DEFAULT 0,
PRIMARY KEY (id)
);

create table table_two (
id int unsigned not null AUTO_INCREMENT,
table_one_id int unsigned not null,
PRIMARY KEY (id),
FOREIGN KEY table_one_id REFERENCES table_one(id) ON DELETE CASCADE
);

 

Here are the two queries I wish to combine:

 

 

$sql1 = "SELECT id, name FROM table_one";
$stmt = $conn->query($sql1);

// Cycle through result
while($fetch = $stmt->fetchObject())
{
   $table_one_id = $fetch->id;
   $name = $fetch->name;

   $sql2 = "SELECT COUNT(*) FROM table_two, table_one WHERE table_two.table_one_id = table_one.id";
}

  

 

This would give a result like:

 

Table One Id

Table One Name

Table Two Row Counts Linked

1

'this is first record in table one'

6

2

'this is second record in table one'

9

3

'this is third record in table one'

1

4

'this is fourth record in table one'

0

[Muddy_Funster]

Try

SELECT Count(table2.id), table1.id, table1.name FROM table1 RIGHT JOIN table2 ON (table1.id = table2.table_one_id) GROUP BY table1.id, table1.name ORDER BY tabkle1.id ASC

Let me know how you get on, I hav't tested that at all, and can't remember if it's a count(field) or count(*) that you want.

[fenway]

Uh, not RIGHT JOIN -- so uncoventional.

[Muddy_Funster]

you would preffer

...LEFT JOIN table2 ON (table2.table_one_id = table1.id)...

?

[fry2010]

great, thanks for your help. I really dont understand how you can solve this problem so easy, where do you learn to do this kind of thing?

[Muddy_Funster]

Collages and Universities can help. There is also a huge amount of reasource online.  I would suggest getting a good book or two on SQL and database design if you want to make a serious go of it though, but don't have the luxuary of taking classes.  It may seem costly to fork out £60 - £80 in literature, but it all depends on how much value you put on the time it will save you.

 

Although TBH, some of the better members on here (of which I assure you, I am not one) could make a killing holding webinars once a month.

[fry2010]

thanks, I think I will invest in a book. Served me well before when I got one on php and css.

[fenway]

Collages and Universities can help.

Doubtful.

[ebmigue]

thanks, I think I will invest in a book. Served me well before when I got one on php and css.

Try a book by Chris Date, titled Introduction to Database Systems (8e). It has been around for 30 years already (eight editions in all). So you are assured that a wide range of topics and problems are covered by that book when it comes to DBMS. As a bonus, the book has a chapter devoted to the discussion of "object-oriented programming" and how it translates to the relational model.

 

Collages and Universities can help.

Doubtful.

I doubt that doubt.