By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Joining single row table with multi...

P: n/a
Here's my problem:

I built a system for data entry and export based on a schema given to
my by the state. The output of the data is fixed-width, and a good
number of the fields in each row default (never change) for our
particular organization. On the db end, to save space, rather than
create these columns in the main data tables and simply give them
default values for every record, I created a "defaults" table with
them. One row, very small in size. What I need to do now is run a
query to join a data table to the default table, giving me the
fixed-width export that the state's system can read.

For example:

SELECT Defaults.PROVID, Clients.SSN, Clients.ClientID, Clients.Last,
Clients.First, Defaults.SUBCONT
FROM Clients
INNER JOIN Defaults
ON Defaults.Linker=Clients.Linker;

Clients has 5 columns and 3 rows
Defaults has 3 columns and 1 row

Linker is a column I placed in both Clients and Defaults and the field
equals 1 in both cases. There is a relationship between the two
tables based on this field.

When I run this, I get no output. What am I doing wrong?

Thanks,
Andrew
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 13 Oct 2004 08:07:13 -0700, sp****@gnt.net (Andrew) wrote:

You don't need a join, you need a cartesian product. The linker fields
are not needed.
Drag both tables on the query design surface, and DO NOT join them.
Select * from each table, and you have the result set you're looking
for.

-Tom.

Here's my problem:

I built a system for data entry and export based on a schema given to
my by the state. The output of the data is fixed-width, and a good
number of the fields in each row default (never change) for our
particular organization. On the db end, to save space, rather than
create these columns in the main data tables and simply give them
default values for every record, I created a "defaults" table with
them. One row, very small in size. What I need to do now is run a
query to join a data table to the default table, giving me the
fixed-width export that the state's system can read.

For example:

SELECT Defaults.PROVID, Clients.SSN, Clients.ClientID, Clients.Last,
Clients.First, Defaults.SUBCONT
FROM Clients
INNER JOIN Defaults
ON Defaults.Linker=Clients.Linker;

Clients has 5 columns and 3 rows
Defaults has 3 columns and 1 row

Linker is a column I placed in both Clients and Defaults and the field
equals 1 in both cases. There is a relationship between the two
tables based on this field.

When I run this, I get no output. What am I doing wrong?

Thanks,
Andrew


Nov 13 '05 #2

P: n/a
I tried that, and get a totally empty set. If I use the join, I at
least get 1 row, although it's totally empty. The fields listed are
not the only columns in the table, there are more but I kept it
smaller to keep it simpler. Also, the fields from DEFAULT have to be
placed between various fields in CLIENT so Selecting * won't work.

Any other ideas?
Nov 13 '05 #3

P: n/a
On 14 Oct 2004 05:37:27 -0700, sp****@gnt.net (Andrew) wrote:

A carthesian product doesn't return 0 rows, unless (one of) the
underlying tables are empty.

-Tom.

I tried that, and get a totally empty set. If I use the join, I at
least get 1 row, although it's totally empty. The fields listed are
not the only columns in the table, there are more but I kept it
smaller to keep it simpler. Also, the fields from DEFAULT have to be
placed between various fields in CLIENT so Selecting * won't work.

Any other ideas?


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.