SQL, recordsets and multiple table queries.

[harkusa]

I have a table and a recordset containing the following SQL. which displays a members adverts if they have any offers or not. This works fine.

SELECT * FROM (

SELECT * FROM  advert LEFT JOIN offer ON advert.advert_id = offer.to)

a WHERE a.user_id = (SELECT members.user_id from members where members.username = sessionuser)

Basically the offers table contains an offers.from and an offers.to foreign key to the adverts parent table.

I can display the offers.to foreign key and link it to the advert table fine.

Now the problem I have is I need to link the offer.from foreign key to the advert table.

I created a 2nd recordset using a URL parameter offers.from. to access the adverts table again for the information (on a different record)

Although this works using the 'test' feature in the SQL dialogue box (in which the offer.from parameter is entered), when I run it nothing is displayed. I have even displayed offer.from in the table to make sure it is getting that value, which it is.

Is this because..........

                             I have chosen to display only records for the the users particular login in the 1st recordset.

                            recordset1 and recordset2 are not linked?

Any other suggestions.

[harkusa]

Well, it not a problem with the user login as I have changed the offers to be for the same user,just to eliminate this possibility

So it must be to do with the actual recordset

Any Suggestions

[bregent]

>I can display the offers.to foreign key and link it to the advert table fine.

>Now the problem I have is I need to link the offer.from foreign key to the advert table.

>I created a 2nd recordset using a URL parameter offers.from. to access the adverts table again for the information (on a different record)

Why do you need to create a 2nd recordset. Can't you just create a table alias for advert and join to the offers.from column?  Or do you need two recordsets for another reason?

Also, why are you using a derived table? I don't see a reason for it.

[harkusa]

To be honest I did try to do it like this but couldn't then select the binded objects in dreamweaver as the syntax was incorrect.

Is it possibe to have an INNER JOIN with an alias anyway?

In any case It would be better to go the SQL route.

Any suggestion on how the code would look?

[bregent]

>To be honest I did try to do it like this but

>couldn't then select the binded objects in

>dreamweaver as the syntax was incorrect.

OK, I'm not exactly sure what you tried, but one of your problems could be due to your use of Select *.  It's almost always better to explicitly state the list of columns you want. This is extemely important if you have two tables that share column names as they will be ambiguous. And if you are aliasing a second adverts table as I suggested, then you are guaranteed to have ambiguous columns so you need to list the columns explicitly.

[harkusa]

I need an offers table because it contains information on the transaction between the two adverts.

E.g. status (open, complete etc) and date.

[bregent]

>I need an offers table because it contains

>information on the transaction between the two adverts.

Yes, I understand that. My questions were:

1) Why are you complicating things by using a derived table?

2) Why do you need two recordsets? Why not just pull all the data into a single recordset?

[harkusa]

Your right. I don't need to use a derived table or 2 recordsets,

It's just the SQL already has an LEFT JOIN and a subquery and adding another subquery is starting to get beyond my knowlege of SQL a bit.

That's why I tried to do it the recordser way.

It's obvious now I'll need to do it in SQL but I don't know how.

To clarify I need to.

• Look up the offers table
  
• Access the advert table using both the TO AND FROM (advert_id's) separately. which are foreign keys to the advert table. I need to do this because I need detailed advert information on the user's advert (to) and the sending user's advert (from).
  
• but only for the current user.
• It needs to be a LEFT outer join as the user's adverts may have no offers.
  

[bregent]

Can you show us the tables/column definitions and provide a few bits of sample data?

[harkusa]

sure

advert

advert_id (inc)

username

o_make    (o = offered)

o_model

w_make     (w = wanted)

w_mode

...

...

..

Offer

offer_id (inc)

user_id (foreign to member.user_id)

from (foreign to advert.advert_id)                   (e,g, from = advert id of the sending advert)

to    (foreign to advert.advert_id)                (e.g. to = advert id of the receiving advert)

status

date

..

..

Member

user_id (Inc)

username

...

...

...

Basically a user would created an advert with details of what they are offering and what they want.

Another user, who has similarly placed an advert would make an offer on it based on their (from) advert and the advert they are interested in (to)

The first post has the existing SQL which lists the from adverts by user fine.

[bregent]

I'll take a closer look when I get into the office later. Is there any relationship between advert.username and member.username?

[harkusa]

advert does not have a username. it has a user_id, which is a foreign key of member.user_id. A session variable sessionuser is set up in the login program which is a check to see if the advert belongs to the user.

This is not my main concern though, its accesses two separate advert records based on the offer.from and the offer.to.

I've done some research on this and the best advice i've had is to use alias

Select x.*,

     y1.advert_id,

     y2.advert_id

From advert x

LEFT

JOIN offer y1

     ON x.advert_id = y1.from

LEFT

JOIN offer y2

     ON x.advert_id = y2.to

However, this basically does the opposite to what I want. Which is an alias for the advert table

Here's the closest I've got

Select a.*, b.*, offer.*

FROM  advert a, b

LEFT

JOIN offer

     ON a.advert_id = offer.from

LEFT

JOIN offer

     ON b.advert_id = offer.to

SQL, however does not allow the alias on advert. I did try to give offer an alias too but that didn't help

Surely there must be a solution to this.

Maybe I need to go back down the recordset route?

[bregent]

>advert does not have a username.

But in your table definitions above, you show it as having a username. I must admit, I really do not understand your data model or your business problem.  For example, would a single advert row contain details for both offers and wanted? Again, please provide sample data to help clarify the problem.

[harkusa]

Here's an example

                                   Offered                                          Wanted                                                                offers   

ID               offered_Make     offfered Model     wanted_make     wanted_model          ID    offered_make     offered model  advert ID

29               Gibson                Les Paul                Fender                Stratocaster                 1    Fender                  Stratocaster      32

                                                                                                                                      2    Gibson                  SG                   33

30               PRS                    MIRA

• So a user (user 1) posts an advert, This contains what they are offering and what they want. This is stored in the advert table - this user has two adverts 29 and 30 (It is not essential Offered and Wanted to BOTH be filled in.)
  
• As you can see user 1 has received 2 offers for  advert 29 , offer 1 and 2, with are from adverts 32 and 33 respectively (these would of been posted by other users. They haven't received offers for advert 30 yet, which is the reason why we need the left join to display all records regardless.
  
• So basically one line in the table needs to show the advert the user has posted,along with any offers received, which is easily retreived by using the very first SQL statement I post. (using offers.to)
  
• The rest of the offers section at the end is causing the problem as it needs to read the advert table again to determine which advert the offer is coming from. (using offer.from) and display the offered_make and offered_model for it.

Hope this makes it a bit clearer.

Another issue I have is with bindings using aliases, Is there a way of showing the alias in dreamweaver to make the fields easier to identify.

[harkusa]

Sorry

Hope this looks a bit better

offered                            wanted                   offer

ID       o_make  o_model  w_make  w_model  ID advert o_make o_model

29       Gibson    LP            Fender     Strat          1  32         Fender   Strat

                                                                             33     Gibson   SG

30       PRS       Mira

[bregent]

OK, first of all I suspect that you could better normalize the design by either

1) Create a new table with a single column to store the item with a flag to indicate whether it is wanted or offered. The table would link to the advert and have a row for each advert/item combination, or

2) Create two tables; items_wanted and items_offered

I think either way would simplify this query, and probably more that you haven't thought of yet.

Regardless, your current issue is that you have a table with a many to many relationship with itself. That's not a problem. You are using the offer table to link the advert to itself. The problem is that you want to link the table to itself on two fields. Still, it's do-able. The two options here are whether you want all details on a single row. For example, if a advert has items wanted and items offered, do you want to display both corresponding offer details on the same row? Or, create two lines for each advert/offer combo - one for the wanted items and one for the offered items.

For the first, you need 5 tables. The original advert table and offer table, and another two aliases for advert and one alias for offer. The SQL would be something like:

SELECT advert.username, advert.o_make, advert.o_model, advert.w_make, advert.w_model, advert_1.o_make, advert_1.o_model, advert_2.w_make, advert_2.w_model, advert_1.username
FROM (((advert LEFT JOIN offer AS offer_1 ON advert.advert_id = offer_1.to) LEFT JOIN advert AS advert_1 ON offer_1.from = advert_1.advert_id) LEFT JOIN offer ON advert.advert_id = offer.from) LEFT JOIN advert AS advert_2 ON offer.to = advert_2.advert_id;

For the second option, you could create a union to list the items offered on one line and the items wanted on another line. The syntax between the two UNION queries would be similar, having the advert table outer joined to the offer table which is then outer joined again to an alias of the advert table. The only difference is the joins on the 'from' and 'to' would be reversed.

[harkusa]

I've attached the exported data for the advert and offer tables (advertandoffers.xls) and also the table structures for both of these tables. (open in wordpad to make sense of it).

I think the problem is a lot simpler than you think.

The wanted/offered columns in the advert table are just text fields which aren't linked to anything so an offer would only ever be sent to the advert, not the advert offered/wanted columns, if you see what I mean, and yes, I do need all the information displayed on one line.

The only fields we are interested in really are advert.advert_id, offer.from and offer.to. Once we have those identified., the other fields can just be added.

I can even forget about bringing the user details into the equation at this as it is separate issue, which I know how to solve anyway.

This peice of sql_code gets the vast majority of information I need.

SELECT * FROM advert

LEFT JOIN  offer ON advert.advert_id = offer.to

This displays all advert details with the offers, they have received (some adverts will have 1 or more offers, others will have none).

All I need to do is take the about SQL and read the advert table again to get the advert details based on offer.from (i.e. details of what is being offered), but all on the same line. We already have the offfer,from details from the above query.

You're right it is a many to many relationship with itself bases on two separate fields.

I ran the SQL code you sent me it does returns the correct fields (offered make and model). But it doesn't display ANY other information.

[harkusa]

Oh and by the way, your right, it is a swapping system.

[bregent]

The attachments for the ddl came throught but the xls was blank - opening it complains about missing sheet001.Try attaching again or PM me.

Anyway, based on your comments here is the sql that would be a starting point. Obviously you need to add any other columns you need by using a fully qualified name.

SELECT advert.advert_id, advert.o_make, advert.o_model, advert.w_make, advert.w_model, offer.offer_id, offer.from, offer.to, advert_1.advert_id
FROM (advert LEFT JOIN offer ON advert.advert_id = offer.to) LEFT JOIN advert AS advert_1 ON offer.from = advert_1.advert_id

[harkusa]

Here's some, more complete info with proper data and test results. I  included a .doc file in place of the xls file.

I'll try running the SQL now.

Thanks a lot for your help by the way.

Adam

[bregent]

OK, here you go:

SELECT advert.user_id, advert.advert_id, advert.O_Make, advert.O_Model, advert.W_Make, advert.W_Model, offer.offer_id, offer.from, advert_1.O_Make, advert_1.O_Model
FROM (advert LEFT JOIN offer ON advert.advert_id = offer.to) LEFT JOIN advert AS advert_1 ON offer.from = advert_1.advert_id

[harkusa]

Success !!!!!

Thanks so much.

I needed to tinker with the code a bit, basically it wouldn't work without the aliases x , y and z on the aliased table (advert_1) elements.

I also added the functionality to only display a logged in user's own adverts.

SELECT  advert.advert_id, advert.o_make,  advert.o_model, advert.w_make, advert.w_model, offer.offer_id, offer.from, offer.to, advert_1.advert_id x, advert_1.o_model y, advert_1.o_make z

FROM (advert LEFT JOIN offer ON advert,advert_id = offer.to)

INNER JOIN members m

ON advert.user_id = m.user_id AND m.username = sessionuser

LEFT JOIN advert AS advert_1 ON offer.from = advert_1.advert_id

Here's the results, attached.

Hope this helps anyone else with a similar problem.

I can get on with the rest of my project now

Thanks again.

[bregent]

Great. To filter by user you can do it the way you currently have it - joining the members table, or do it the way your had it in your original post - using a subquery. Whichever is more readable for you.

And yes, you do need to alias the column names if they are listed more than once, as Dreamweaver doesn't seem to be able to correctly resolve fully qualified names in server behaviors.

[bregent]

>Hope this makes it a bit clearer.

I think it does now. Am I correcting in thinking that this is some sort of Swap board - where merchandize is swapped between users? Knowing this helps define the problem. If it is not a swapping arangement, then I think you need to do a little more normalization. Let me know and in the mean time I'll work on a SQL solution.

[bregent]

Also, please post the data into an html table or attach a spreadsheet. It's still difficult to see how the columns of data are aligned.