Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
>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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
>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.
Copy link to clipboard
Copied
I need an offers table because it contains information on the transaction between the two adverts.
E.g. status (open, complete etc) and date.
Copy link to clipboard
Copied
>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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Can you show us the tables/column definitions and provide a few bits of sample data?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
I'll take a closer look when I get into the office later. Is there any relationship between advert.username and member.username?
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
>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.
Copy link to clipboard
Copied
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
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Oh and by the way, your right, it is a swapping system.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
>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.
Copy link to clipboard
Copied
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.
Find more inspiration, events, and resources on the new Adobe Community
Explore Now