SQL Question....I want to get out of the office already...can someone help?? please? ?

Question

ok...so...here is the thing...I have two tables, one named Venues and one named Venues Categories. When a user performs a search, the search query is equaled to the field `venues`.`name`, which then calls `venues`.`id` and equals it to `venues_categories`.`id_venue`, if these two are equal, it extracts `venues_categories`.`id_category` and outputs the `venues`.`name`...someone else made the database...i get stuck with them putting it on two different tables :| I need help with the SQL asap, i need to get out of the office :(

Here is what I have thus far:
$stacres = mysql_query("SELECT DISTINCT venues.name, venues.id_city, venues.id, venues.id_state, venues.img, venues.description FROM venues, venues_categories WHERE venues.id = venues_categories.id_venue GROUP BY venues.name");

The DISTINCT is because each venue has 3 venue_categories to its ID. Please help :D and if you cant, then please star :)

Answer 1

I too, am having trouble with the question you are asking. The thing I see missing here is the search term. What does the user enter? and what field are you comparing it to?



Post that update, and I'll help you. I do PHP and MySQL all day, and I join a lot more than 2 tables together. Do a "show create table venues" and "show create table venues_categories". Post those, and what the search term is, and I'll reply back.



One more thing, if you are grouping by venues.name, the DISTINCT won't narrow your results. But, again, I don't see a search term here in the query....

Answer 2

They're put in two different tables because the tables are used for two different things. Venues contains information about venues, which are identified by venues._id. Venues_Categories (you have it as venues categories above, but I think that's a typo) says what category a venue is in if you have the venue ID (venues_categories.id_venue).



Some of the information you are requesting comes out of the venues_categories table (the category id among other things), and some comes out of the venues table. What you're doing is a "join" on the venue id in both tables (called venues.id in the table "venues", and id_venue in the venues_categories table). This briefly produces a denormalized "view" that you can extract the information from.



Your SQL looks OK, but the output is missing hte venues_categories.id_category field. I would suggest:



$stacres = mysql_query("SELECT DISTINCT venues.name, venues.id_city, venues.id, venues.id_state, venues.img, venues.description, id_category FROM venues, venues_categories WHERE venues.id = venues_categories.id_venue GROUP BY venues.name");



All I have done is add ", id_category" to the select statement. Since this is a unique column name and not present in the venues table, that should be sufficient.



Good luck!

Answer 3

You really have 2 choices:



You can group by everything in your select statement. The records you get back will be huge and pretty useless.



Or you can drop the group by, use an order by, and format the "report" in whatever language this is running in (php?). You might want to use something from http://www.dynamicdrive.com/ to pretty it up. (I have a few pages that use the grid to display database select results. Grid on the html page [with a little javascript], programming in the php page and AJAX to communicate between them.)

Answer 4

I'm sorry but I've read this three times and for the life of me, can't find a question in your statement. Can you re-phrase what it is you are trying to achieve with the query?



-Aaron