Three table join problem

Dev Shed Forums Sponsor:

June 25th, 2009, 02:56 PM

almo2001

Contributing User

Join Date: Mar 2009

Posts: 99

Time spent in forums: 1 Day 5 h 8 m 46 sec
Reputation Power: 1

Three table join problem

Hi!

I have three tables:

tbl_hordepacks (H):
hordepack_id user_id name

tbl_hordepacks_creatures (HC):
hordepack_id creature_id count

tbl_user_collection_creatures (UCC):
user_id creature_id count

Given a hordepack_id and a creature_id, I want the count in the hordepack and the collection.

So I need count from HC (easy) and count from UCC where creature_id matches the given one and the user_id matches the user_id of the given hordepack_id.

I keep getting weird results that look like there's a problem doing

SELECT hc.count AS packcount, ucc.count AS collectioncount.

Do I need to do two different queries? My instinct is that there's a single query that will do this, but I'm really having a hard time finding it.

Here's my current attempt:

Code:

SELECT hc.count AS packcount, ucc.count AS collectioncount
FROM tbl_hordepacks_creatures AS hc
JOIN tbl_user_collection_creatures AS ucc
	ON hc.creature_id = ucc.creature_id
JOIN tbl_hordepacks AS h
	ON h.user_id = ucc.user_id
WHERE h.user_id = 1 AND h.hordepack_id = 1 AND hc.creature_id = 128

But this returns every count from HC without restricting by hordepack_id or user_id.

UPDATE:

Code:

SELECT hc.count AS packcount, ucc.count AS collectioncount
FROM tbl_hordepacks_creatures AS hc
JOIN tbl_user_collection_creatures AS ucc
	ON hc.creature_id = ucc.creature_id
JOIN tbl_hordepacks AS h
	ON h.user_id = ucc.user_id
WHERE h.user_id = 1 AND hc.hordepack_id = 1 AND ucc.creature_id = 128

This returns the correct result, but twice. I'm pretty sure I don't fully understand what's going on when I JOIN twice.

Last edited by almo2001 : June 25th, 2009 at 03:05 PM.

June 26th, 2009, 07:12 AM

srisa

Contributing User

Join Date: May 2006

Location: I'm sneaking up behind you.

Posts: 1,178

Time spent in forums: 2 Weeks 6 Days 15 h 46 m 15 sec
Reputation Power: 229

Try this

sql Code:

Original - sql Code
SELECT hc.count AS packcount, ucc.count AS collectioncount FROM tbl_hordepacks_creatures AS hc JOIN tbl_user_collection_creatures AS ucc ON (hc.creature_id = ucc.creature_id AND hc.creature_id = 128 AND hc.hordepack_id = 1) JOIN tbl_hordepacks AS h ON (h.hordepack_id = hc.hordepack_id AND h.user_id = ucc.user_id AND h.user_id = 1)

 
SELECT hc.COUNT AS packcount, ucc.COUNT AS collectioncount
FROM tbl_hordepacks_creatures AS hc
JOIN tbl_user_collection_creatures AS ucc
    ON (hc.creature_id = ucc.creature_id
       AND hc.creature_id = 128 
       AND hc.hordepack_id = 1)
JOIN tbl_hordepacks AS h
    ON (h.hordepack_id = hc.hordepack_id 
       AND h.user_id = ucc.user_id
       AND h.user_id = 1)

Comments on this post

almo2001 agrees: Seems to work, thanks!

__________________
Why do we always seek someone, something or some thought, are we afraid of ourselves?
"The love of one's country is a splendid thing. But why should love stop at the border?" - Pablo Casals

June 26th, 2009, 10:20 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,691

Time spent in forums: 2 Months 1 Week 3 Days 9 h 46 m 6 sec
Reputation Power: 2459

i'd like to suggest a slight change to the query

the first table in the FROM clause should be the "driving" table, such that it determines the fewest rows to be joined to the other tables

thus, conditions on the driving table belong in the WHERE clause, and all other conditions can/should be added to their respective ON clauses

Code:

SELECT hc.COUNT AS packcount
     , ucc.COUNT AS collectioncount
  FROM tbl_hordepacks_creatures AS hc
INNER
  JOIN tbl_user_collection_creatures AS ucc
    ON ucc.creature_id = hc.creature_id
INNER
  JOIN tbl_hordepacks AS h
    ON h.hordepack_id = hc.hordepack_id 
   AND h.user_id = ucc.user_id
   AND h.user_id = 1
 WHERE hc.creature_id = 128
   AND hc.hordepack_id = 1

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

June 26th, 2009, 04:28 PM

srisa

Contributing User

Join Date: May 2006

Location: I'm sneaking up behind you.

Posts: 1,178

Time spent in forums: 2 Weeks 6 Days 15 h 46 m 15 sec
Reputation Power: 229

Quote:

Originally Posted by r937

So, the rows from the table in the FROM clause are first retrieved based on the WHERE clause (if given) and then the JOIN's are performed.
Is that correct?

June 26th, 2009, 05:29 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,691

Time spent in forums: 2 Months 1 Week 3 Days 9 h 46 m 6 sec
Reputation Power: 2459

Quote:

Originally Posted by srisa

So, the rows from the table in the FROM clause are first retrieved based on the WHERE clause (if given) and then the JOIN's are performed.
Is that correct?

um, sort of

i hope i did not give the impression that you can influence the sequence of execution by what you put into the WHERE clause for the first table in the JOIN

you actually can dictate the sequence of join (with special keywords), but this is rarely a good idea

the database optimizer will figure out the best execution sequence based on the logical construction of the query requirements, examining all the different ways of doing it, and choosing the fastest

... and it's way better at that than we SQL developers are

all i wanted to say was that with a little bit of thought, it's pretty easy to tell in any given situation (and i've seen thousands of them) which table is the driving table

so you write the query in that manner, thus adding ~immensely~ to the value of the SQL itself as a means of documention wtf the query is doing

have you ever come back to a complex query several months later, and wished you knew what it was doing and why?

that's what coding style is all about

June 29th, 2009, 02:50 PM

almo2001

Contributing User

Join Date: Mar 2009

Posts: 99

Time spent in forums: 1 Day 5 h 8 m 46 sec
Reputation Power: 1

Quote:

Originally Posted by r937

Code:

SELECT hc.COUNT AS packcount
     , ucc.COUNT AS collectioncount
  FROM tbl_hordepacks_creatures AS hc
INNER
  JOIN tbl_user_collection_creatures AS ucc
    ON ucc.creature_id = hc.creature_id
INNER
  JOIN tbl_hordepacks AS h
    ON h.hordepack_id = hc.hordepack_id 
   AND h.user_id = ucc.user_id
   AND h.user_id = 1
 WHERE hc.creature_id = 128
   AND hc.hordepack_id = 1

Ok, I think I get it. Thanks a lot for the help!

ETA: one question: is "AND h.user_id = 1" redundant? As long as h.user_id matches ucc.user_id, it should be fine since ucc_user_id must be 1 since user 1 owns that pack...

Last edited by almo2001 : June 29th, 2009 at 03:08 PM.

June 29th, 2009, 03:32 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,691

Time spent in forums: 2 Months 1 Week 3 Days 9 h 46 m 6 sec
Reputation Power: 2459

Quote:

Originally Posted by almo2001

...since ucc_user_id must be 1 since user 1 owns that pack...

hmmmm.....

June 29th, 2009, 03:42 PM

almo2001

Contributing User

Join Date: Mar 2009

Posts: 99

Time spent in forums: 1 Day 5 h 8 m 46 sec
Reputation Power: 1

Quote:

Originally Posted by r937

hmmmm.....

WHERE hc.creature_id = 128 AND hc.hordepack_id = 1

One hordepack belongs to one user. So once we specify hordepack_id = 1, we have effectively specified the user since tbl_hordepacks has hordepack_id, user_id, and name.

I've tried removing WHERE user_id = 1 and it seems to work the same.

Viewing: Dev Shed Forums > Databases > MySQL Help > Three table join problem

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!