Re: sql statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anders Altberg (x_pragma_at_telia.com)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 14:37:48 +0200

Hello Javier
CREATE cursor Cities (Codcity C(2), namecity C(10))
INSERT INTO Cities VALUES ('01','New York')
INSERT INTO Cities VALUES ('02', 'London')
INSERT INTO Cities VALUES ('03', 'Madrid')

CREATE CURSOR Customers (Custcity C(2), namecustomer C(10))
INSERT INTO Customers Values('01','smith')
INSERT INTO Customers Values ('01', 'brown')
INSERT INTO Customers Values ('02', 'beckham')
INSERT INTO Customers Values ('02', 'Millar')

CREATE CURSOR suppliers (suplcity c(2), namesupplier C(10))
INSERT INTO Suppliers Values ('01', 'John')
INSERT INTO Suppliers Values ('01', 'Peter')
INSERT INTO Suppliers Values ('03','Juan')
INSERT INTO Suppliers Values ('03','Jose')

SELECT C1.namecity,C2.namecustomer,S1.namesupplier ;
FROM Cities C1 ;
LEFT JOIN Customers C2 ON C1.codcity=C2.custcity ;
LEFT JOIN Suppliers S1 ON S1.suplcity=C1.codcity ;
INTO CURSOR Q1

result:
Namecity Namecustomer Namesupplier
New York smith John
New York smith Peter
New York brown John
New York brown Peter
London beckham .NULL.
London Millar .NULL.
Madrid .NULL. Juan
Madrid .NULL. Jose

I don't know why you don't want New York smith Peter and
othere possible ocmbonations.
PS It's always appreciated if you present sample data in runnable code.

-Anders

"javier Barrera (jbarrera@yahoo.com)" <javier Barrera
(jbarrera@yahoo.com)@discussions.microsoft.com> wrote in message
news:4CE89C78-8161-4456-A597-82C2F2D3AD4C@microsoft.com...
> I am trying to write a sql statement but I have had no luck. I have three
tables: city, customers, suppliers, the primary key is codcity
>
> City
> Codcity, namecity
> 01 New York
> 02 London
> 03 Madrid
>
> Customers
> Custcity namecustomer
> 01 smith
> 01 brown
> 02 beckham
> 02 Millar
>
> suppliers
> suplcity namesupplier
> 01 John
> 01 Peter
> 03 Juan
> 03 Jose
>
> I need a result as:
>
> City customer supplier
> New York smith john
> New York brown Peter
> London beckham .null.
> London Millar .null.
> Madrid .null. Juan
> Madrid .null. Jose
>
> Any suggestions?
>
> Thanks in advance,
> Javier.
>
>
>
>

Relevant Pages

  • Re: Loop. How can I do this?
    ... The loop that displays the cities do it the following way: ... London Madrid ... Lisbon New York Tokio ...
    (comp.soft-sys.matlab)
  • Re: been running a day camp all week...
    ... say, London, where I grew up? ... New York is worse for traffic, ... the roads and it's not a lot better round here at times. ... big cities are big cities and a different matter entirely. ...
    (uk.people.support.depression)
  • Re: Alternative to the 3rd Runway Alternative
    ... It wouldn't exactly help north WCML overcrowding nor the Northern ... cities, would it, despite those having a far worse capacity problem ... I agree it's a London centric solution, but you don't explain why it's ... to cities in the North. ...
    (uk.railway)
  • Re: Remembering August 6, 1945!
    ... I know Akihito did issue some apologies to the countries that Japan ... Harris was taught how to burn cities by the Luftwaffe. ... standing on the roof of the Air Ministry watching the City Of London ... The daylight bombing was a mistake in that it allowed the RAF ...
    (sci.military.naval)
  • Re: European trip ?s
    ... >> hasn't been to London or Paris or Rome. ... This is just the way big cities are. ... I want to see some of the culture. ... The same is true of NYC. ...
    (rec.travel.europe)