Many to one to Many only want 1 record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Many to one to Many only want 1 record

Author

Topic

Midnight Developer
Starting Member

3 Posts

Posted - 2008-05-13 : 02:53:46

Hi All,
I am very new to SQL.
Please forgive if this is a repost of an already answered question.

Expectation of data:
1. Used to be added into Excel Forms to print out and be used
2. Management Queries
3.

Create a list of specific tasks per each user, similar to a calendar.

The basic structure of the database has 2 parts.
Both of these connect to on single table after multiple joins of one to many.

The main problem I am having is that because of the one to manys, the database queries are growing exponentially.
I would like there to be only a specific record added into a table each time a user adds a new task, so that it can be called soley by the user ID, or other IDs at a later date.

To try and answer my own question:
The concept I may be missing is how the data is originally inputted, table structure, both, or how to write queries dynamically.

So here are the tables simplified:

Sponsor ID
Sponsor Name

         Join 1 to X

Protocol ID
Sponsor ID
Protocol Name

         Join 1 to X

Study ID
Protocol ID
Study Name

         Join 1 to X

OU ID
Study ID
First OU
Secound OU
Box Type ID
Shipment Type ID
Coordinator Name ID
Tempurature ID

      Join X to 1

Box ID       Shipment ID       Coord ID     Temp ID
Box Name     Ship Name         Coord Name   Temp Name

Here is some Data:

Sponsor Protocol Study FirstOU Coord Name Box Type Ship Temp
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -70
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -40
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -20
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Ambient
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Refrig
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 4mL Daily -70

As the Data shows, each possible combination of values is available.
What I am looking for is only conceptually is one record, yet for the possibliligy for a different person to ship a different First OU/BoxType/ShipType/Temp

Please let me know if there is anything in here I am missing to help get the answer.

Thanks
Mic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 04:10:07

We want some sample data from your tables before providing you with the solution. Can you post it please?

Midnight Developer
Starting Member

3 Posts

Posted - 2008-05-15 : 01:54:45

Here is the query being used.

SELECT        Sponsor.SponsorName, Protocol.Protocol, StudyID.StudyIDName, OU.FirstOU, zCoordinatorName.CoordinatorName, zShipmentType.ShipmentType
FROM            Sponsor INNER JOIN
                         Protocol ON Sponsor.SponsorID = Protocol.SponsorID INNER JOIN
                         StudyID ON Protocol.ProtocolID = StudyID.ProtocolID CROSS JOIN
                         OU CROSS JOIN
                         zBoxType CROSS JOIN
                         zCoordinatorName CROSS JOIN
                         zShipmentType CROSS JOIN
                         zTempurature

I thought I had added sample data: Pasted data from above.
This is actual data from the query.
If you want to know what data I expect, it would be only one specific line of data out of all of these.

Here is some Data:

Sponsor Protocol Study FirstOU Coord Name Box Type Ship Temp
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -70
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -40
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -20
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Ambient
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Refrig
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 4mL Daily -70

Noobishness is next to Godliness

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 02:13:09

quote:
Originally posted by Midnight Developer

Here is the query being used.

SELECT Sponsor.SponsorName, Protocol.Protocol, StudyID.StudyIDName, OU.FirstOU, zCoordinatorName.CoordinatorName, zShipmentType.ShipmentType
FROM Sponsor INNER JOIN
Protocol ON Sponsor.SponsorID = Protocol.SponsorID INNER JOIN
StudyID ON Protocol.ProtocolID = StudyID.ProtocolID CROSS JOIN
OU CROSS JOIN
zBoxType CROSS JOIN
zCoordinatorName CROSS JOIN
zShipmentType CROSS JOIN
zTempurature

I thought I had added sample data: Pasted data from above.
This is actual data from the query.
If you want to know what data I expect, it would be only one specific line of data out of all of these.

Here is some Data:

Sponsor Protocol Study FirstOU Coord Name Box Type Ship Temp
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -70
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -40
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily -20
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Ambient
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 2mL Daily Refrig
J&J T-Protocol JO6 S0101 Raul Vargas 9x9 4mL Daily -70

Noobishness is next to Godliness

If you look at sample data, you've different values coming for last two fields. Which value do you prefer out of them when we make all these data into one row? the first value, last value or any one value for Box Type, Ship Temp fields?

Midnight Developer
Starting Member

3 Posts

Posted - 2008-05-24 : 04:35:36

Hey I figured it out!!!

Im such a doosh

I was using the express edition query writer,
and because of my noobishness did not notice that the query was using Cross Joins.

I re-wrote the query to have all inner joins an whala!

Noobishness is next to Godliness

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources