Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can anyone help with query ?

 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All, I'm having a bit of trouble writing a query and wondered if someone might take a quick look.

I have 2 tables (using MYSQL by the way) , Dives and Locations, there is a location_id in the dives tables linking to location_id in the locations table.
I'm trying to build a resultset that I can show in a JTable which includes the location_id, LocationName and a Count() of the number of dives the user has been to at a particular location.

So far my query is this...

select l.location_id,l.locationname,l.country,count(d.location_id) as numdives
from dives d
inner join locations l on d.location_id = l.location_id
where d.user_id = 1
group by l.location_id,l.locationname

and that works ok. Note the where user_id = 1 (this will come froma user bean when the user logs in, but for now i'm just hardcoding it to 1)

My puzzles begin when I have added a field to the locations table, PrivateLocation(boolean). By default this will be set to true when a location is added to the table but I want to allow users to 'share' their locations with other users..

So for example I added a new location for user #2 and set the privatelocation to false. When user #1 lists the locations using the query above I also want to show the new location. I thought I could just change the WHERE clause to WHERE d.user_ID = 1 OR l.privatelocation = false, But this lists the new location ok but the NUMDIVES column will show 1 dive which isnt true, user #1 never dived there, it was user #2.

I hope that explains my problem without being confusing, Any advice really appreciated..

Thanks,

Dave.
 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave,

If I understand your situation correctly, you want to know all of the private locations a particular user has been as well as all of the non-private. I toyed with this for a few minutes, and I think I have something that will work for you. I structured my tables just a bit differently, but I think you will see the point.



When UserID 1 has not been to a non-private location, you will see



Well, with my data anyway. To test, I added a dive for UserID 1 and Location 2, and the Dives count changed properly to 1.

The trick is in the UNION. I tried to do it with a single query and just couldn't.

Give it a try. It should work for you.

Peace,
Phillip
 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brilliant, That works for me too.

Thanks very much :-)

 
Phillip Koebbe
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome!

I've received so much from various communities on the internet, and I'm just thrilled that I can give something back!

Peace,
Phillip
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic