Query Help

Databases
1

Hello Everyone,

Im going crazy trying to figure this out.

I have 2 tables of vehicle options,

options & options_extra

options has

category, subcategory, veh_option, type

i would have:
Select * from options where category = ‘auto’ AND subcategory = ‘car’

second table
options_extra

category, subcategory, veh_option, type, did

im trying to get a list of all the “options” ( matching category and subcat ) plus all the options_extra that match the cat and sub cat and where did = 189

i think im getting myself more confused the more i read

i was trying SELECT options.veh_option, options.type, options.category, options.subcategory FROM options inner JOIN options_extra on options.veh_option = options_extra.veh_option WHERE options.category = ‘automobile’ AND options.subcategory = ‘car’ AND options_extra.did = ‘189’

any help please.
TIA

2 
SELECT veh_option
     , type
     , category
     , subcategory 
  FROM options 
 WHERE category = 'automobile' 
   AND subcategory = 'car' 
UNION ALL
SELECT veh_option
     , type
     , category
     , subcategory 
  FROM options_extra 
 WHERE category = 'automobile' 
   AND subcategory = 'car' 
   AND did = '189'

:cool:

3

Thank you VERY VERY MUCH. I was going nutz

4

I have one more question.

is there a way within this query to get the count of specific “type” - there are 17 different types of options ( audio, standard, comfort etc. ) I was using individual querys to get the number of specific options of each type like (9) audio options, 12 - standard etc

5

not easily, not within this query

could you show one of the individual queries you were using

6

i was doing

SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category=“automobile” and subcategory=‘car’ group by type order by type

&

SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category=“automobile” and subcategory=‘car’ and did=‘189’ group by type order by type

then if( $rows[type] == “additional” ) { setting a var for each , then adding together

just seemed like alot of querys

7

in your last post, you show both queries pulling stats from the options table – did you actually mean the options_extra table for the scond one?

plus, both those queries will give you counts for all types, i.e. one count per type, so i don’t understand why you say you needed a “lot of queries” – just two of them, one per table

8

yes, i was trying to copy and paste real quick before i had to run out,

what i ment was i had the querys in for the counts, the option table is pretty static

SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category=‘automobile’ AND subcategory=‘car’ group by type order by type
will give me:
audio 11
body 4
comfort 10
engine_cyl 6
engine_ltr 13
extras 22
fuel_type 6
int_style 4
standard 3
status 3
title 3
trans 3
trans_drive 3
trans_type 4
warranty 3

audio for example then
if( $rows[type] == “audio” ) {

	if( strpos(" ".$result2[Audio] , $rows[veh_option]) > 0  ) {
   	 	$audio = $audio."<input type='checkbox' name='audio' value='$rows[veh_option]'CHECKED id='$rows[recID]' >$rows[veh_option] <br>";
			} else { 
		$audio = $audio."<input type='checkbox' name='audio' value='$rows[veh_option]' id='$rows[recID]' >$rows[veh_option] <br>";
	 }

$aud++;     
if($aud == 3){
	$audio = $audio."</td><td valign='top'>";
	$aud=0;
	}

}

knowing I have 11 audio options i can / by 3 and get a nicely formated 4 column check box list.

dealers wanted to start adding to the options, so i made the options_extra , they all just add the type in as “additional” which was easy to just have that as a seperate checkbox list, and num_rows was my count,

$count=mysql_num_rows($result1);
$divid = round($count / 4);

so when a specific dealer added 60 “additional” options i still had a nicely formated 4 column checkbox list,

now they wanted to start seperating these into there approperate “type”

is when i started trying to join the tables, by changing a few of the “additional” to “audio” resetting the aud counter at 3 produced more columns,

i think im just overcomplicating this in my head. but the objective is to just keep everything aligned to 4 colums

i wasnt sure if there was a way to run Distinct on the tables after the union, rather then seperatly on both adding them together to get a “count” that i can divide by 4
and use that as my

if($addition == $divid){
	$additional = $additional."</td><td valign='top'>";
	$addition=0;
	}
9

you’ve completely lost me on all that

by the way, when you have GROUP BY, the DISTINCT becomes redundant and should be removed