[SOLVED] unsure how to go about complex join query using most of my db (4 tables)

[pixelgirl]

Hello, I have had some excellent help from people on this forum several times, thought ive tried only to post when im really stuck

 

I am writing some SQL code using PHP with a MySQL DB, and am coding a dynamic query that adds sql query conditions depending on what a user inputs in to a form.

Ive managed to get most of it working, but would appreciate some hints or help for the last bit, as it is the most complex, and I dont really know where to start. Ive already had some help with part of the query below (the floor .. as bits) from someone else one the forum

 

The sql im using at the moment is (without the conditions, i add them on after):

 

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height -((floor(h.height/4))*4)) AS height_inches, 
h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age -((floor(h.age/12))*12)) AS age_months, 
h.sex, hi.thumbName
FROM horse h, horseimages hi
WHERE h.horseID = hi.horseID
AND hi.thumbName is NOT NULL;

 

At the moment it retrieves the horse data (age, height etc..) and the related image for that horse.

I want to extend the query to include another table containing the different categories.

There is an added problem in that because im using the categories in other sections of the site, I also have a lookup table for the categories.

The category table itself contains the category names, and a unique id to identify each category.

The lookup table contains a reference to a unique horse id, a unique category id, and unique owner id (as well as a unique id for the lookup row itself).

I want to be able to query a list of the category names from the category table(all included in the sql given above), with only the category names listed that relate to horse ids selected.

 

I can provide more info if necessary (such as table structures) but just want some advice on how to go about this problem.

 

Any help is appreciated

 

[friedemann_bach]

A friend of mine is into horses and your questions remind me of her, somehow. So I like to help again

 

SELECT c.category_name
FROM categories c, lookup l
WHERE l.horseID = $requested_horse_id
  AND l.categoryID = c.categoryID

 

The ID for the requested horse is given to the query in "$requested_horse_id." I assume that your category table contains the name of the category in the column "category_name" and your lookup table contains the columns "horseID" and "categoryID".

 

This will get all categories corresponding to a specified horse. If you want the categories for a number of horses, modify it to "... WHERE l.horseID IN ($your_horse_ids) ...".

 

I think you should execute this query separately from the horse data query. So you have one query to get all the horse data and another query to get all the corresponding categories. There is surely a way to fuse these two queries into one, but I am quite sure that it would look pretty complicated. I think it is easier to handle this way. Hope this works!

 

Don't hesitate to ask if you need more horse help

 

[pixelgirl]

Thank you!!

 

I was thinking of seperating the two queries as it was getting rather complicated, but wasnt sure how to go about it...

 

With so many combinations and ways of presenting the data my mind was boggling over how to get exactly what it want :S

 

My brain has currently shut down so ill have a go with this tomorrow. Ill post for more help if i need it (but hopefully shouldnt..)

 

 

[Barand]

Sounds like the structure is like this

[pre]

horse_image          horse              lookup              category

=============        ==========        ==========          ==========

horseID    ---------  horseID  -------- horseID      +----  categoryID

thumbName            name              categoryID ---+      categoryName

                      height            ownerID

[/pre]

 

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height % 4) AS height_inches, 
h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age % 12) AS age_months, 
h.sex, hi.thumbName, c.categoryName
FROM horse h 
    INNER JOIN horseimages hi ON h.horseID = hi.horseID
    INNER JOIN lookup l ON h.horseID = l.horseID
    INNER JOIN category c ON l.categoryID = c.categoryID
WHERE hi.thumbName is NOT NULL; 

[pixelgirl]

Wow thanks! Much appreciated!

 

The diagram makes a lot of sense, wish id thought of it, i might have got somewhere!

 

Only issue is I think my webpage will then display the multiple versions of each horse, when I really want one instance of each horse and then the rows of categories so I can search through them for matches. I may be able to code the page to ignore multiple versions, but it might be easier if its in a seperate query. (or i may be showing myself up as a noob and there is an obviously best way to do this?)

 

I wouldve thought (though thanks a lot for your uber query ) seperate queries is the way to go?

 

What would anyone else recommend?

 

Again, thanks for the help 

 

 

 

 

[pixelgirl]

After thinking about the best way to go about it, I thought I could use select distinct with Barands query to only bring back a single row for each horse.

 

However, having looked at an article here http://www.databasejournal.com/features/postgresql/article.php/3437821 about why not to use distinct (which also uses a join with several tables), I decided to try and rejig the query using sub queries to get back the exact data I required. The query from Barand was very helpful as it cleared up how and what I wanted to get out of the tables.

 

I tried using a subquery on the category table, as I dont need to show the categories, just bring back rows where they have particular categories.

 

Ive posted the sql below for anyone whose interested. Also, if someone could have a look at it and let me know if it seems to do what I described/doesnt look horribly wrong etc.. it would be appreciated

 

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height -((floor(h.height/4))*4)) AS height_inches, h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age -((floor(h.age/12))*12)) AS age_months, h.sex, hi.thumbName

FROM horse h, horseimages hi

WHERE h.horseID = hi.horseID

AND hi.thumbName is NOT NULL

AND h.horseID in (

SELECT l.horseID

FROM horsecat c, lookuphorsecat l

WHERE l.catID = c.horseCatID

AND c.category in

('Dressage', 'Show Jumping')

);

[pixelgirl]

OK, I hate to keep asking questions but this one has me stumped.

 

With Regard to Order by and Group by:

 

Looking at the subquery from the sql I posted above:

 

AND h.horseID in (
SELECT l.horseID
FROM horsecat c, lookuphorsecat l
WHERE l.catID = c.horseCatID
AND c.category in
('Dressage', 'School Master')
);

 

I want to order the results from this so that the horses with the most matches (i.e both dressage and school master, or more if more are listed) are shown at the top, with horses with fewer matches further down the results.

 

When I do a query in my database without any ordering etc.. (with category also shown for clarity) I get these results:

 

horseID 	category
86 	      School Master
86 	      Dressage
79 	      Dressage
78 	      School Master
78 	      Dressage
89 	      Dressage

 

I want to 'group' them so that the two 86 results are at the top (because 86 has both) and the two 78 results are next (because this also has both) (or the first two vice versa as order isnt important beyond the above condition) then 79 and 89 further down, because they each have one.

 

I looked up 'order by', but realised this is not what I want, as it would either order it by id (which wouldnt help) or category (which also isnt what I want)

 

I then looked up 'group by' - This is where i got confused.

 

The group by example here http://www.w3schools.com/sql/sql_groupby.asp only shows an example for aggregate functions such as SUM, which is also what i dont want.

 

I then tried to look for other examples of grouping, but havent come up with anything useful.

 

Could anyone tell me if theres a way of getting what I do want?

 

Again any help is appreciated

[Barand]

Joins, as I had, are more efficient than subqueries.

 

WHERE l.catID = c.horseCatID

AND c.category in

('Dressage', 'Show Jumping')

 

It looks like the first condition limits you to a single category and then the second condition tries to select from two categories (but then I don't know your exact structure)

 

Also, you can accomplish

 

(h.height -((floor(h.height/4))*4)) AS height_inches

 

with

 

h.height % 4 as height_inches (modulo operator)

 

as I did in my query

[Barand]

Forget my comment about single category - I just realised it's the join condition. One reason I hate that syntax that mixes joins with selection criteria and always use "... JOIN ... ON ..."

[pixelgirl]

Sorry, I had noticed you had changed the calculation to the mod operator but forgot to thank you

 

I did try and use it, but somehow came up with a MOD() function (which i think is only oracle SQL) and that didnt work (i now realise why) - though curiously it did do something, but just not the correct answer. Im not the best at maths, so gave up and did it the way I could understand which seemed to work (thought i know its the basic way.)

 

The thing is, from what I understand (thought my understanding is rather fuzzy, do correct me if im wrong), the join adds the table data to the output, which i dont really need, as it produces a duplicate horse row for every category (i.e the horse data is repeated for the number of categories each horse has.) I want to display the number of rows of data that is returned from the query, so If duplicate rows are produced it will display 5 versions of each horse.

 

This is why I was using the nested select. I did think I could use select distinct, but then that throws away a lot of data, and from what ive read shouldnt really be used unless it cant be avoided.

 

Again any ideas are helpful, and thanks for the help so far

[Barand]

try

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height % 4) AS height_inches, 
h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age % 12) AS age_months, 
h.sex, hi.thumbName, GROUP_CONCAT(c.categoryName SEPARATOR ',') as categories, COUNT(*) as catcount
FROM horse h 
    INNER JOIN horseimages hi ON h.horseID = hi.horseID
    INNER JOIN lookup l ON h.horseID = l.horseID
    INNER JOIN category c ON l.categoryID = c.categoryID
WHERE c.category in
('Dressage', 'Show Jumping')
GROUP BY h.horseID
ORDER BY catcount DESC

 

PS

MySQL supports

 

MOD(height, 4)

height MOD 4

height % 4

[pixelgirl]

Thanks very much for spending so much time and effort helping me and suggesting all the complex joins etc..

 

I tried the query posted above, but its producing some rather odd results (e.g the 2 categories showing up multiple times each) and most importantly the thumbname is null for all but one result.  (the thumbnail gives the filepath to show the pictures of horses on my site)

 

Ill stick to the query I posed above, but im interested in the group_concat bit.

 

Ive found a webpage which basically shows what i want to do here: http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html, and ive just had a go and i think ive got it working!!!

 

One final question (famous last words...)

 

if I have this sql:

                                                        SELECT l.horseID,
						COUNT(*) as cnt,
						GROUP_CONCAT(c.category SEPARATOR ', ')
						as cat_list
						FROM horsecat c, lookuphorsecat l
						WHERE l.catID = c.horseCatID
						AND c.category in
						('Dressage', 'School Master')
						GROUP BY l.horseID
						ORDER BY cnt DESC;

which produces three fields, how do I put it in to the code below when i want to put it as a nested statement which will require only one field (the horse ID) to be returned?:

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height -((floor(h.height/4))*4)) AS height_inches, h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age -((floor(h.age/12))*12)) AS age_months, h.sex, hi.thumbName
						FROM horse h, horseimages hi
						WHERE h.horseID = hi.horseID
						AND hi.thumbName is NOT NULL
						AND h.horseID in (.....CODE ABOVE HERE

 

 

 

[pixelgirl]

the only way I could think of doing it was by creating the nested bit as a view - but i bet thats the horribly wrong way of doing it (and I know I should be doing it with joins, but it works my way and i understand it. I cant get my head around joins :S)

 

once this is done i think ive got it all fixed 

[Barand]

OK, I'm still plugging JOINS. I can't see your data so I don't know where the duplicate cats come from but this may help (note DISTINCT in GROUP_CONCAT and COUNT)

 

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height % 4) AS height_inches, 
h.breed, h.price, h.colour, h.age, h.age, floor(h.age/12) AS age_years, (h.age % 12) AS age_months, 
h.sex, hi.thumbName, 
GROUP_CONCAT(DISTINCT c.categoryName SEPARATOR ',') as categories, 
COUNT(DISTINCT c.category) as catcount
FROM horse h 
    INNER JOIN horseimages hi ON h.horseID = hi.horseID
    INNER JOIN lookup l ON h.horseID = l.horseID
    INNER JOIN category c ON l.categoryID = c.categoryID
WHERE c.category in
('Dressage', 'Show Jumping')
GROUP BY h.horseID
ORDER BY catcount DESC

[pixelgirl]

Doh - everything is right except the thumbname is null for all except the first 

 

i really dont understand why :'(

 

EDIT - after comparing my query and the one you have done its missing the bit

AND hi.thumbName is NOT NULL

 

as there are three entries for each horse in the horseimages table, it was picking out the first entry, which has no thumbname entry as only one out of three has it. after i put the above bit in it brings back exactly what I want.

 

Thank You SO much for all your help

 

Its really appreciated as I would have done terrible things with my db (views *shudders*) when all I had to do was create a four table join with a group count (sounds simple now... lol)

 

Anyway, thanks again!

[Barand]

Do you have several records for each horse in the image table, some of which could be null?

 

If so, add

 

AND hi.thumbName IS NOT NULL

 

to the WHERE clause

[pixelgirl]

Lol, i was just editing that in my previous reply.

<<< see previous for greatful thanks etc ..