SQL Join query question

[HaveTrouble]

I have 3 tables to join together. Table 1 has 1 row (always 1 row), table 2 has 2 and table 3 has 3. Instead of producing 6 rows, is it possible to produce only 3 rows ?

eg. table 1
inv=123

table 2
inv=123 item=1
inv=123 item=2

table 3
inv=123 date=5/12/03
inv=123 date=5/13/03
inv=123 date=5/14/03

Desired output:
inv=123 item=1 date=5/12/03
inv=123 item=2 date=5/13/03
inv=123 item=2 date=5/14/03

Is it possible to use SQL query to produce desired output ?

 

[vongrunt]

Can you describe/explain the way tables 2 and 3 are supposed to be linked?

Perhaps table2.item represents number of rows from table3?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mercwrought]

yes. however table 3 must have a way/ reason only to chose 1 row from table 2 like date < '5/13/2005' selects item one.
while > select item 2. there has to be a reason to select one and not the other

 

[HaveTrouble]

There is no link between table 2 and 3. Both tables only link to table 1 via "inv" field but not to each other.

 

[mercwrought]

lets ask this diffrently then why do you want the output you desire?

 

[HaveTrouble]

I am trying to put the data in a text file with required format. The format is:

invnumber,item#,date

I am trying to cut down the rows created so the file size won't get too huge if there are lots of data to be exported.

 

[Catadmin]

Try this:

Select t1.inv, t2.item, t3.date
from table1 t1 
left outer join table2 t2
on t1.inv = t2.inv
left outer join table3 t3
on t1.inv = t3.inv
where <put a conditional if you want>

The where clause is completely optional of course.

Does this get you what you're looking for?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mercwrought]

I am trying to cut down the rows created so the file size won't get too huge if there are lots of data to be exported.

my question becomes the validity of said data. If there is no way to decide which of the two rows in table 2 that a given row in table 3 is to connect to then both rows are correct. I beleve that there is a way to decide, however the connection may not be apparent or even that the 2 tables have no business being joined together. perhaps you can give us better idea of what you are doing.

 

[Catadmin]

Merc has a good point.

I just realized your example above has a problem. You want output that repeats the second line in Table 2, but there is no way to join Table 2 to Table 3 without going through Table1. So how do you know what dates go with what items in the Inv row?

My code isn't going to reproduce your desired output exactly. You'll end up with something like

inv=123 item=1 date=5/12/03
inv=123 item=2 date=5/13/03
inv=123 item=<NULL> date=5/14/03

And there's no guaruntee that the dates will match with the correct item #.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[HaveTrouble]

There is no relationship between table 2 and table 3. So it doesn't matter if item=1 is joined with date=5/13/03 or 5/14/03 as long as all the dates are showing. item=2 doesn't neccessary need to show in line #3 since it's already shown in line #2.

I am receiving an EDI file where there are loops. Eg. 1 invoice is in 1 EDI file. In the invoice, there are multiple dates (invoice date, ship date, etc.. 1 loop per date) and multiple items. Dates are applied to the whole invoice instead of the individual items. I am trying to convert this EDI to the format that user wants.

 

[HaveTrouble]

Or, is there a way a have SQL query add a sequence number ?

eg. select * from table2

instead of returning:
inv=123 item=1
inv=123 item=2

it will return:
inv=123 item=1 seq=1
inv=123 item=2 seq=2

I can do the same thing to table 3 then join 2 tables by seq number. Is it workable ?

 

[Catadmin]

You can always "create" a calculated column in SQL. Whether or not it'll be what you want it to be is another problem. @=)

The best way I can think of is to pull your data into a Temporary Table that has an Identity column (which auto assigns your identity column & you can call that column Seq or anything else). Then do your select off of the Temp Table.

You may need two tables to do it. Lookup Temporary Tables in Books Online for details on creating them.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???