Complicated Join

Complicated Join

Post by Ross » Fri, 24 Aug 2001 01:23:40



Using SQL 2000 Standard Edition
I have two tables that I would like to join. Here is an example.
Table1(
UniqueIdent
Field1
field2
field3
field4)

Table2(
UniqueIdent
Field5
field6
field7)
The user want to do something like this:
select field1, field2,field3,field4,field5,field6,field7 from table1, table2
where (table1.uniqueident = table2.uniqueident)

This is fine I know this can be done. The problem is that there could be
records in Table1 that don't exist in Table2 and viceversa. The user would
like all rows returned but where the UniqueIdent is the same they want to
records joined. So the out put could look like:
Field1   Field2   Field3  Field4   Field5   Field6   Field7
   1          2           3           4          5         6           7
                                                     8         9          10
  11       12           12          14

Can this be done at the server level? I am sure I can write a program to
combine everything but I would rather have a stored procedure or something
like that.
Ross

 
 
 

Complicated Join

Post by oj » Fri, 24 Aug 2001 01:43:40


select t1.*, t2.*
from t1 full outer join t2 on t1.id=t2.id

-oj


Quote:> Using SQL 2000 Standard Edition
> I have two tables that I would like to join. Here is an example.
> Table1(
> UniqueIdent
> Field1
> field2
> field3
> field4)

> Table2(
> UniqueIdent
> Field5
> field6
> field7)
> The user want to do something like this:
> select field1, field2,field3,field4,field5,field6,field7 from table1,
table2
> where (table1.uniqueident = table2.uniqueident)

> This is fine I know this can be done. The problem is that there could be
> records in Table1 that don't exist in Table2 and viceversa. The user would
> like all rows returned but where the UniqueIdent is the same they want to
> records joined. So the out put could look like:
> Field1   Field2   Field3  Field4   Field5   Field6   Field7
>    1          2           3           4          5         6           7
>                                                      8         9
10
>   11       12           12          14

> Can this be done at the server level? I am sure I can write a program to
> combine everything but I would rather have a stored procedure or something
> like that.
> Ross


 
 
 

Complicated Join

Post by Ross » Fri, 24 Aug 2001 06:03:10


Cool Thanks
 
 
 

1. Complicated Joins with comma delimited values?

Hi there,

I am just attempting to build a query to bring back the results of a join,
but I have hit a problem. Everything is stored in a SP
I have 3 tables. Here are my first 2. Pretty straight forward. A member can
spend points on a GIFT.

Table: MemberPointsSpent
MemberID
GiftID
DateSpent

Table: Gifts
GiftID
Gift_Number

Heres the SQL .. pretty simple - no probs.

SELECT Gifts.Gift_Number,MemberPointsSpent.DateSpent,
MemberPointsSpent.Points  From MemberPointsSpent INNER JOIN Gifts
ON MemberPointsSpent.GiftID = Gifts.GiftID WHERE MemberPointsSpent.MemberID

But a gift can contain more than 1 item so I wanted to return another column
which would list all the items of GiftID 3 (for example) like so
"fridge,computer, television" - all seperated by a comma..

Here is my Items table

Table: Items
ItemID
GiftID
Name

So hence GiftID 3 may have a number of records in Items.

I know my recordset should look something like this but just can't seem to
figure this one out
Gift_Number : 3
DateSpent= 30/03/99
Points = 30
Items = fridge, television, microwave

The thing that I can't grasp is how to amend the above SQL to add a column
which would return the items as 1 column but having commas.

I did see the following example list somewhere but I can seem to get it to
work with my existing SQL Statement above.






this for every gift...

Really confused, can anyone give me a helping hand??

Thanks in advance

Ian

2. Creating new table from view

3. Complicated Join Statement

4. Determining if a table exists

5. complicated self-join

6. Unhappy thoughts about pg_dump and objects inherited from

7. How to create a complicated query using Joins

8. Right Join / Hash Joins / Nested Joins

9. Complicated SQL Statement

10. Simple question - complicated solution ?

11. Help! Complicated SQL Statement

12. complicated SQL - need ideas