FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


SQL problem for solving :)





animassacre
Sooo I have to 2 tables:
1.Gallerys with columns - Type (from 1 to 4),Id,Name and some others
2.Photos with columns -Gallery_id,Id and some others

And with this statment:SELECT COUNT(*),Id FROM galleries GROUP BY Type
I get the number of galleries for each type ,( and the latest Id - not important).

OK so far so good.
But i figured out that I need to count only galleries that are in use (present in table photos atleast once in column Gallery_id)

so here is my try to solve the problem,but this doesn't work (I was to naive to even guess it could work like this):

SELECT COUNT(g.Id),g.Id FROM galleries g,photos p WHERE g.Id=p.Gallery GROUP BY g.Type;

PLS HELP.
AftershockVibe
You want to look at using different types of join on your tables.

Try something along the lines of;
Code:
SELECT COUNT(g.Id), g.Id
FROM galleries g
    INNER JOIN photos p ON g.id = p.gallery_id
GROUP BY g.Type


Inner joins remove any records which are not linked together using id. Note that this will also remove any photos which have a gallery id which does not exist.

Technically you could have used only a right join but then any photos with gallery ids without a record would be counted too.
Manofgames
Shouldn't p.Gallery be p.Gallery_id?
kv
Are you getting a mysql error or does it return wrong data? Looks like your query is ok.
animassacre
Thanks for the attention.

AftershockVibe -I tryed your query,but it gives the same wrong data as my version
I will definetly read more about those joins(Thanks for the hint), but right now i have to finish the site i'm doing, so I will leave the empty galleries to show in the count.

Manofgames - 0.o , It was 1:31 pm.
In the table it's just gallery, but i decided to add more meaning to the name in the post LOL

kv -- i answered your question at the top of this post



The problem is still present but thanks anyway.
kv
If I understand it correctly, you want to

1. Get the count of galleries - grouped by type
2. The gallery has to be in use
3. You want to get the count of galleries - not the count photos in the galleries.

A normal join or a inner/outer join will not do in this case. Simply because these joins repeat the gallery data for every photo in the gallery. You will have to use a subquery instead.

Like this

Code:

SELECT COUNT(g.Id), g.Type FROM galleries g WHERE EXISTS (select * from photos  where Gallery=g.id) GROUP BY g.Type
animassacre
Sad
kv - Yes it's just what i need, but for some reason i can't make it to work. It returns an empty query. It works as it sould, if there is no GROUPing and COUNTing, but it do nothing when i'm grouping the rows to count the columns.
kv
I have tested the query I posted. It worked for me. Can you check the data once? Also, you could create some dummy tables with little data (3-4 rows) and test it using mysql console. It will be much quicker to test.

Here are the sql statements I used to create tables and data. You can change the name of tables and test it.

Code:

CREATE TABLE `galleries` (
  `id` int(11) default NULL,
  `Type` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `galleries` (`id`, `type`) VALUES
  (1,'1'),
  (2,'1'),
  (3,'2'),
  (4,'2');

CREATE TABLE `photos` (
  `Gallery` int(11) default NULL,
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `photos` (`Gallery`, `id`) VALUES
  (1,12),
  (3,13),
  (3,34),
  (4,56);


SELECT COUNT(g.Id), g.Type FROM galleries g WHERE EXISTS (select * from photos  where Gallery=g.id) GROUP BY g.Type;

animassacre
that's odd, Sad

it doesn't work on my server.
it returns 0 Rows ,too

my mySQL Server is version 5.0.41-community-nt

PS:i tryed it on one other server (version: 5.0.45 on Unix) and it works Sad
What should i do?? Re-install the server ?Or is it some option.
kv
You can try uninstalling your current server and installing a older, more stable version of the server. However, I find it strange that a particular version of server doesn't run a straight forward query like this.
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.