Retrieving one value from one table and many from another

[kamirazvan]

Hi..<br>
I have two tables that are linked with a One-Many relationship. I need to retrieve one value from one table and all the associated entries from the other table.<br>
<br>
This is for a query on a web site and the output needs to be like:<br>
<br>
State 1<br>
- County 1<br>
- County 2<br>
- County 3<br>
State 2<br>
- County 1<br>
- County 2<br>
<br>
No matter what I do, I keep getting State 1, County 1, State 1, County 2, ... you get the picture.<br>
<br>
Any help is greatly appreciated.<br>
<br>
Kami

 

[carp]

Karni -<br>
I don't think you will be able to do this with standard SQL. However, if you are using Oracle (or an RDBMS with a language similar to PL/SQL) you can get your results with a couple of cursors.<br>
<br>
DECLARE<br>
<br>
CURSOR state_cursor IS<br>
SELECT state FROM tablea;<br>
<br>
CURSOR county_cursor IS<br>
SELECT ' -''¦¦county FROM tableb<br>
WHERE state = v_state;<br>
<br>
BEGIN<br>
<br>
FOR v_state IN state_cursor LOOP<br>
dbms_output.put_line(v_state.state);<br>
<br>
FOR v_county IN county_cursor LOOP<br>
dbms_output.put_line(v_county.county);<br>
END LOOP;<br>
END LOOP;<br>
<br>
END;<br>

 

[kamirazvan]

Hi..<br>
thanks for your reply. I am -currently using MS-Access as the backend but will port it SQL Server 7.0 shortly. For now I am trying find a solution that I can implement using Access.<br>
<br>
thanks..<br>
<br>
Kami

 

[calahans]

Hi Kami,<br>
<br>
It sounds to me that you would be better served trying to do this through code. If its for a web site are you using ASP? If so this should be easy enough to code. It would involve getting you recordset and looping through the recordset checking state = state(n-1). If they are the same don't print the state, if they are different do.<br>
<br>
Cal

 

[kamirazvan]

Thanks.. you are right. I was hoping I can do a simple SQL statement and not write the ASP code. FrontPage 2000 is a pain when it comes to messing around with the database code it generates.<br>
<br>
I am working on manually programming the database section and doing as you suggested.. once again thanks.<br>
<br>
Kami

 

[MapMan]

Try this...<br>
<br>
<br>
Select TableA.*<br>
From TableA LEFT OUTER JOIN <br>
TableB ON<br>
TableA.Key = TableB.Key<br>
<br>
This should shall all the records in TableA and only records in TableB that match the key.<br>
<br>
HTH,<br>
<br>
MapMan<br>

 

[carp]

True, but that wasn't the original problem. <br>
Kami wants to avoid having repeated values from being displayed.

 

[yaffle]

I'm not 100% sure about the MS-Access SQL syntax, but how about:<br>
<br>
select nvl(b.county, b.state)<br>
from<br>
(select a.state, a.county<br>
from<br>
(select state, '- ' ¦¦ county county<br>
from table_a<br>
union<br>
select state, null county<br>
from table_a) a<br>
group by a.state, a.county) b;<br>
<br>

 

[nitram]

Hmmm. Not sure you need the 'group by', yaffle. <br>
I suggest the following: (MS SQL server syntax)<br>
<br>
SELECT ISNULL(c, s)<br>
FROM <br>
(SELECT state s, ' - ' + county c<br>
FROM county_table<br>
UNION<br>
SELECT state s, NULL c<br>
FROM county_table) a<br>
ORDER BY s, c<br>
<br>
Assumes county_table with columns (county, state). Where 'state' is a FK. (If the 'state' table has a synthetic PK then you'll need to join to the 'state' table to get the name.)<br>
<br>
Should translate to Access OK if it supports 'isnull'.<br>
<br>
(For Oracle: read 'isnull' as 'nvl' and '+' as '¦¦', from what I remember!)<br>
<br>
Cheers,<br>
Martin<br>
<br>
<p>Martin Ramsay<br><a href=mailto:Martin.Ramsay@company-net.co.uk>Martin.Ramsay@company-net.co.uk</a><br><a href=

http://www.company-net.co.uk>My

employer</a><br>Looking for interesting work in Canada - any offers ?