Statement to show value from joined tables

[apex82]

I have the following tables:

Unit
Unit ID
Unit code
Unit description

System
System ID
System code
System description
Business ID ~ Joined to Business table
Cover ID ~ Joined to Cover table

Business
Business ID
Business code
Business description
Unit ID ~ Joined to Unit table
Logo ID ~ Joined to Logo table

On a web page I want to group all the System code(s) in the System table by the Unit code in the Unit table.

Please can some help me code the SQL statement to do this? Is it possible to do this via two tables?

Thanks.

 

[bborissov]

Pleas, post some example data for all tables and what you want as a result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[apex82]

Yes, I hope you are able to make sense of this. I'm not sure how else to display the tables.

System

System ID - System code - System description - Business ID - Cover ID

1 - DD - LARGE - 1 - 4
2 - CC - SMALL - 1 - 4


Unit

Unit ID - Unit code - Unit description

1 - GA - This is the Description
2 - GB - This is the Description
3 - GC - This is the Description


Business

Business ID - Business code - Business description - Unit ID - Logo ID
1 - AA - Business AA - 1 - 1
2 - BB - Business BB - 3 - 5


I want to be able to list all the System codes for a specific Unit code. i.e. just for GA.

 

[apex82]

In more laymans terms I’d need to search the ‘unit ID field within the business table to match a value i.e GA this would then give me the system codes from the system table.

The system table is linked to the business table and the business table is linked to the unit table on the values from my first post.

Thanks.

 

[bborissov]

SELECT *
FROM Business
INNER JOIN System ON Business.UnitId = System.SystemID AND
                     System. UnitCode = 'GA'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[apex82]

Thanks very much.

I have tweaked the statement so there are no errors:

str_SQL = "SELECT * FROM Business INNER JOIN System ON Business.UnitId = [System_ID] AND [Unit Code] = 'HC' ORDER BY [System Code] ASC;"

My only issue with the above statement is: if I have 5 records in the business I will see 5 results for the System codes and if I have 5 records in the business and 8 Systems I will see 5 results for the System codes...etc

There can be more than one system attached to a business.

Is there a way to tweak this statement so it just shows all the systems?

Thanks.

 

[bborissov]

No, if you have ONE business and TWO systems attached to that business you will see TWO records, where the business fields will be the same, but systems will vary.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[apex82]

Not sure if I have explained this properly.

What I mean is if:

- I have 4 records for business

- 3 units with the code GA attached to business 1

- 2 units attached to business 2 with the code GB

- 4 units attached to business 3 with the code GC.

All the 3 GA's would show but only the first of the 2 units with the code GB will show and none for GC.

The number of units shown with this SQL statement seems to reflect the amount of records.

 

[bborissov]

OK, see my first suggestion.
Give me an example data AND result you want, but not as an explanation (my English is not enough) just shouw what your want from that example data AS resultset.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[apex82]

Thanks for your help.

System

System ID - System code - System description - Unit code - Business ID - Cover ID

1 - BG - LARGE - GA - 1 - 4
2 - CH - SMALL - GA - 2 - 4
3 - CO - LARGE - GA - 1 - 4
4 - A0 - SMALL - GB - 1 - 4
5 - BG - SMALL - GB - 1 - 4
6 - DN - SMALL - GC - 3 - 4
7 - EQ - SMALL - GC - 3 - 4
8 - H8 - SMALL - GC - 1 - 4
9 - AH - SMALL - GD - 2 - 4
10 - AI - SMALL - GD - 1 - 4


Unit

Unit ID - Unit code - Unit description

1 - GA - This is the Description
2 - GB - This is the Description
3 - GC - This is the Description
4 - GD - This is the Description


Business

Business ID - Business code - Business description - Unit code - Logo ID
1 - AA - Business AA - GA - 1
2 - BB - Business BB - GB - 2
3 - CC - Business CC - GC - 3
4 - CC - Business CC - GD - 4


If I have the above tables I want to be able to search the Unit code and display the System codes that are associated.

E.g if I searched Unit code GD – I would get the following result:

AH, AI

 

[apex82]

bborissov, did I give enough information?

Thanks.

 

[apex82]

I think i've worked it out, this is providing me with the correct data.

str_SQL = "SELECT * FROM Business INNER JOIN System ON Business.Business_Id = System.BusinessID AND [UnitID] = '1' ORDER BY [System Code] ASC;"