Known Participant

Question

SQL, recordsets and multiple table queries.

Forum|Forum|16 years ago
February 2, 2010
3 replies
2317 views

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.

Server side applications

This topic has been closed for replies.

H

harkusaAuthor

Known Participant

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

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

B

bregent

Participating Frequently

>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?

B

bregent

Participating Frequently

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.

>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.

B

bregent

Participating Frequently

>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.

H

harkusaAuthor

Known Participant

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?

B

bregent

Participating Frequently

>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.

H

harkusaAuthor

Known Participant

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded