SQL Select Question 1

[tatonka]

Can anyone help me with the following problem?
I want to be able to output all of tables ones contents plus associated table two contents using SQL.

Example...
TABLEONE
table_name field_name
STAFF STAFFID
STAFF NAME
STAFF JOBTITLE
CLIENT CLIENTID
CLIENT NAME

TABLETWO
table_name field_name tag_name expression
STAFF STAFFID STAFFID STAFFID
STAFF JOBTITLE JOBTITLE JOBTITLE
STAFF JOBTITLE JTNAME JOBTITLE+NAME
CLIENT CLIENTID CLIENTID CLIENTID

SQL QUERY OUTPUT
table_name_a field_name_a tag_name expression
STAFF STAFFID STAFFID STAFFID
STAFF NAME
STAFF JOBTITLE JOBTITLE JOBTITLE
STAFF JOBTITLE JTNAME JOBTITLE+NAME
CLIENT CLIENTID CLIENTID CLIENTID
CLIENT NAME

Hope this makes sense.

 

[DSummZZZ]

I think this may be what you want:

SELECT * FROM tableone, tabletwo ;
WHERE tableone.table_name == tabletwo.table_name;
INTO CURSOR MyCursor


Dave S.

 

[TheRambler]

try this:

select a.table_name, a.field_name, b.tag_name, b.expression from tableone a, tabletwo b where a.table_name = b.table_name

 

[TomKane]

In standard sql the command is union

select * from table1
union
select * from table2

Not sure if it's the same in Foxpro - but it's worth a try.

Best of luck...

 

[tatonka]

Thanks for your replies gents but unfortunately none of them solved my problem.

DSummZZZ and mlv1055
Both solutions output 11 records (I am expecting 6)
The WHERE clause associates tabletwo.table_name to each tableone.table_name three times.

I have tried WHERE (tableone.table_name = tabletwo.table_name AND tableone.field_name = tabletwo.field_name) which outputs 4 records.

TomKane
I changed the syntax a little...
SELECT * ;
FROM tableone ;
UNION SELECT * FROM tabletwo
and my language reference manual mentions the UNION clause...
However, FoxPro was having none of it, displaying a message "SELECT's are not UNION compatible"

Is it possible to SQL what I want? or am I left with creating a temp table and populating it using code?

Any other suggestions?

 

[rgbean]

How about:

CREATE CURSOR tableone (table_name C(15), field_name c(15))
INSERT INTO tableone VALUES ("STAFF", "STAFFID")
INSERT INTO tableone VALUES ("STAFF", "NAME")
INSERT INTO tableone VALUES ("STAFF", "JOBTITLE")
INSERT INTO tableone VALUES ("CLIENT", "CLIENTID")
INSERT INTO tableone VALUES ("CLIENT", "NAME")

CREATE CURSOR TABLETWO (table_name c(15), field_name c(15), tag_name c(15), expression c(15))
INSERT INTO tabletwo VALUES ("STAFF", "STAFFID", "STAFFID", "STAFFID")
INSERT INTO tabletwo VALUES ("STAFF", "JOBTITLE", "JOBTITLE", "JOBTITLE")
INSERT INTO tabletwo VALUES ("STAFF", "JOBTITLE", "JTNAME", "JOBTITLE+NAME")
INSERT INTO tabletwo VALUES ("CLIENT", "CLIENTID", "CLIENTID", "CLIENTID")

SELECT a.table_name, a.field_name, b.tag_name, b.expression  ;
 FROM tableone a, tabletwo b ;
 WHERE a.table_name+a.field_name = b.table_name+b.field_name ;
UNION ;
SELECT c.table_name, c.field_name, " ", " " ;
 FROM tableone c ;
 WHERE c.table_name+c.field_name NOT IN ;
   (SELECT table_name+field_name FROM tabletwo) ;
INTO CURSOR Query

While the records aren't in the order you specified, this is the technique you use in FPD/FPW to simulate an Outer Join.

Rick

 

[tatonka]

Problem solved, pure genius Rick.
Thanks to all you guys who replied.

I noticed that a couple of you used alpha extentions in the FROM clause (i.e tableone a), as do some of the examples in my FoxPro manuals. Is this standard protocol or are there benefits to be derived from it? Does it assist macro substitution for example?

 

[rgbean]

tatonka,
The letter after the table name is known as the alias, it's optional and it just makes the SQL statement simpler / shorter. I also could have used:

SELECT t1.table_name, t1.field_name, t2.tag_name, t2.expression  ;
 FROM tableone t1, tabletwo t2 ;
 WHERE t1.table_name+t1.field_name = t2.table_name+t2.field_name ;
...

just as well. It is indeed part of ANSI SQL syntax.

Rick