table join 1

[syenchik]

Hi. I'm trying to write a query that joins fields from two tables. The syntax is eluding me because I need to join two seperate counts from the same table with all of the fields from another table.

The first table is called actionsMB, and I need a count where coid=variable and another where coid=variable and actiontype='Spoke With Decision Maker'

this must be joined with select * from company_local


Any help on the syntax of such a query would be greatly appreciated. Thanks.

Stephen

 

[munkyCmunkyDU]

... I know its a pain but...

You are going to have to give more info in this post to get a helpful response. For SQL issues it's best to include the table structure of the related tables in your post. This will help us help you better. Also, it is sometimes helpful on complicated issues to give a sample data set of the results you are hoping for.

Have Fun!
munkyCmunkyDU

 

[syenchik]

Sorry. The structures are as follows:

actionsMB - coid [int](4), actiontype [varchar](50), and a bunch of information fields (all varchar) that don't matter to this query

The info i need from this table is a count of all records for a particular coid, and a count of all records with actiontype='Spoke With Decision Maker' for the same coid

The second table is:

company_local - ID [int](4) which corresponds to coid in the table above, repname[varchar](50)and a bunch of information fields (all varchar) to be returned by the query where repname is equal to some value.

So a sample recordset would resemble:

company_local.coname - company_local.cosize - ...etc - actionsMB.total1 - actionsMB.total2


Does that clarify?

Thanks

Stephen

 

[munkyCmunkyDU]

Here you go. I have some other ideas that might help you as well, I will put them in following posts - as I need to get back to some real work.

Thanks for this problem - it was a challenge.

Have Fun!
munkyCmunkyDU
<=================================================>

TABLES
-------------------------
TABLE company_local
id int NOT NULL, (PK)
repname varchar (50),
coname varchar (50),
cosize varchar (50)

TABLE actionsMB
actiontype varchar (50),
coid int NULL (FK)

DATA
--------------------------
company_local
1 joe big company big
2 harry med company med
3 al sm company sm

actionsMB
Decision Maker 2
Decision Maker 1
Golf 1
Golf 2
Golf 3
Dinner 1
Dinner 2
Dinner 3

Here is the query... (sorry - its a beast)
---------------------------------------------
select
id,
max(TBL.rep)AS rep,
max(TBL.company) as company,
max(TBL.size) as size,
max(TBL.allActions) as [All Actions],
max(TBL.specific) as [Decision Maker]
FROM
( SELECT
company_local.id,
MAX(company_local.repname) AS rep,
MAX(company_local.coname) AS company,
MAX(company_local.cosize) AS size,
COUNT(allActions.actiontype) AS allActions,
null as specific
FROM
company_local LEFT OUTER JOIN actionsMB allActions ON company_local.id = allActions.coid
GROUP BY company_local.id

UNION

SELECT
company_local.id,
MAX(company_local.repname) AS rep,
MAX(company_local.coname) AS company,
MAX(company_local.cosize) AS size,
null as allActions,
COUNT(specificActions.actiontype) AS specific
FROM
company_local LEFT OUTER JOIN actionsMB specificActions ON company_local.id = specificActions.coid
WHERE (specificActions.actiontype = 'Decision Maker')
GROUP BY company_local.id
) AS TBL
GROUP BY TBL.id


Here are your results...
--------------------------------------------------------------
ID REP COMPANY SIZE ALL DECISION MAKER
1 joe big company big 3 1
2 harry med company med 3 1
3 al sm company sm 2 NULL

 

[syenchik]

Thank you so much. This is the most helpful response that I've gotten on any forum. Any other ideas would be greatly appreciated. This was a tough one.


s

 

[munkyCmunkyDU]

Just some notes about schema design...

One of the primary purposes of using type tables and a relational database engine is to enforce data integrity. Say you were a construction company and had a lot of vehicles, typically a related schema would be designed similar to this:


Table VEHICLE_TYPE
-----------------------
VEHICLE_TYPE_ID int (PK),
TYPE varchar(50),
DESCRIPTION varchar(255)

Table COLOR
-----------------------
COLOR_ID int (PK),
NAME varchar(50)

Table USR
-----------------------
USER_ID int (PK),
NAME varchar(80),
PASSWORD varchar(12)

Table VEHICLES
-----------------------
VEHICLE_ID int (PK),
DATE_PURCHASED datetime,
PURCHASE_PRICE money,
COMMENTS varchar(500),
VEHICLE_TYPE_ID int (FK),
COLOR_ID int (FK),
USER_ID int (FK)

<====================================>
The VEHICLES table is the primary table with the others supporting it.

The only table that actually receives data from the end user is the VEHICLES table. The end user has no access to any of the other tables. This prohibits end users (who developers are trained to think of as utterly stupid and incompetent) from mispelling &quot;red&quot; a bunch of times in the color table and entering &quot;pickup&quot;, &quot;pick-up&quot; and &quot;Pick-em-up&quot; into the VEHICLE_TYPE table.

By closing the door on multiple variations of the same thing, the type table allows the developer to find all of the &quot;red&quot; vehicles quickly and not be worried about missing the &quot;ReD&quot; ones.

again,

Have Fun!
munkyCmunkyDU

 

[syenchik]

A lot of the work I do involves taking other folks' databases, and setting them to work with ASP. In this case it was an Access database that my predecessor had built many years ago. The data structure is actually terrible . . . but I really couldn't change all that much. Initially, the actions table and company table weren't even connected. Ugh.

stephen