Hi there,
I have a database set up with an SQL statement that queries the database to give me this:
--------------------------
| Col A | Col B | Col C | etc... |
--------------------------
| Name | 1 | 3 | 2 |
--------------------------
Basically I want to fill the table out if its shorter than 20 rows and populate it with names in Column A and 0's in the rest of the columns.
Any help would be much appreciated. If you need more of an explanation please let me know.
Regards,
Jonty
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Okay there's far too much SQL, database and script stuff to post. Plus it's spread over numerous pages.
Basically the above table is produced from a table of names with numbers with numbers that reference something else. I want an SQL statement that will get all the names from the original table that are not in the above table and then also set the rest of the columns next to the name to 0. It doesn't seem that difficult and yes I could do it in code but I thought it may be easier to implement with SQL. Basically I thought it may be possible to join the above table to another table that has all the names with the 0's that are not already in the above table.
NB: By the above table I mean the diagram in the first post ;o)
Any better?
Jonty
To do it all in SQL, you will need to do a LEFT JOIN, with at least one IIF statement.
Example:
Obviously you know how to do the LEFT JOIN.
In you SELECT states, for each column that is not 'name' or whatever your key column is, don't just do this:
SELECT table.column
but do this
SELECT IIF(table.column Is Null, 0, table.column)
So, the statement would look something like this:
SELECT table1.name, IIF(table2.Col1 Is Null, 0, table2.Col1) AS ACol1 FROM table1 LEFT JOIN table2 ON table1.name = table2.name
To do this for exactly 20 rows, it would be:
SELECT TOP 20 table1.name, IIF(table2.Col1 Is Null, 0, table2.Col1) AS ACol1 FROM table1 LEFT JOIN table2 ON table1.name = table2.name
Looks to me that you could use a tricked crosstable query.
When you add to the original table one dummy Name (E.g. " ColA") with a row for each number, a simple crosstable query with the name set as rowheader, the nymber as column header and the number as value will show the data in the format you want.
To preserve the data and remove the dummy create a maketable query based on this crosstable query where you select a name <> " ColA"
Getting the idea ?
Nic;o)
I think you could try something like this...
Table1:
Field1
---------
Alfred
Betty
Claire
David
Ellen
Francis
Table2:
Field1 | Field2
-------------------
Alfred | 1
Claire | 3
Francis | 2
...and some SQL a bit like this...
SELECT Table1.Field1, Nz(Table2.Field2, 0)
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
...which should give you something like...
Field1 | Field2
--------------------
Alfred | 1
Betty | 0
Claire | 3
David | 0
Ellen | 0
Francis | 2
LEFT JOINs will show all the records from the left table (Table1) and the records that match from the right table (Table2).
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:
Accept: MrRobin {http:#9645039}
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
stevbe
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: jkpcsPosted on 2003-10-29 at 08:56:27ID: 9643399
Is there some reason you need to do this with one SQL statement or can you use code to do it?