Any SQL Experts?

[Goldberg]

Hi all,

I am having a problem with my website.
I run a footy website and I am trying to simplify a procedure.

Basically each week managers can make bids for new players. The procedure is that they have to put one of their players in the pot and then bid a minimum and maximum fee for the new player.

I think my database design is flawed slightly as I use the Player ID's and do not always pass the name over.

Here is an example of what I am trying to achieve.

Manager 'A' is bidding for the following:

Player Out - Drogba
Player in - Berbatov
Minimum bid £250,000
Maximum bid £1,000,000

This would be sent to a table in the database as follows:

TID (Transfer ID) - Primary field and automated
DATE (Date placed) - 23/10/08
BIDMID (Managers ID) - 10
INFFLID (Player in ID) - 110
OUTFFLID (Player out ID) - 200
MINBID - £250,000
MAXBID - £1,000,000
SUCESS (y, n or p for pending which is default) - p

There are other fields that are irrelevant in this case, they are used for historical purposes.

So, What I am trying to do is extract the info from this table and use some of it to extract data from the Players table in order to display names rather than ID's for the players.

The Players table looks like this:

PID (Player ID which will match INFFLID or OUTFFLID)
PNAME (Players Name)
MID (Managers ID, this is used to list the current manager as only 1 manager can have a player in the league)

again lot's of other fields which are not relevant at this point.

So I want to display a table that would look like this (it should only display 'p' status bids for an individual manager.):

Player Out -- Player In - Min Bid ----- Max Bid ----- Edit - Delete
Drogba ------ Berbatov - £250,000 - £1,000,000 - Link - Link

The trouble I am having is that I can only display the Player out as I am trying to get the name of the player from the Managers ID and clearly the player in does not havea manager linked to him!

I know this may be confusing, it is to me! I could have set up the database better and may need to do so but it is too far into the season now to do this.
Currently I can get around this by using two data grids side by side and sorting on the Transfer ID which is unique, but I need to do it all in one go.

Does anyone have any suggestions? I can provide a lot more detail if anyone is clued up on SQL queries and think that they can help.

There must be a way to do what I am trying to do.

Again sorry if the above description is naff!!

[BBK]

I am sure there is a MUCH better way of doing this, as I'm no SQL expert, but what I would do would be as I'm looping through to display the results simply do a call to the PLAYERS db based on INFFLID. I'm probably missing the point though

[Goldberg]

Yeah but INFFLID is only the information for the Player the manager is bidding for.
I need to display the player been used to go out too.

The easiest solution is probably going to be me having another table with the info I need to display clearly using the actual ID of the Bid.

I might have nearly cracked it the other way but the problem I have is that I am trying to display the Player Name field twice in a single line which is confusing matters... I can do it over two lines or failing that I will have to use a template grid..

The additional Table route is probably the way forward. I can slip that in next week without disrupting things... The issue I have is the site is active 24/7!!

[BBK]

What language are you using? Might be worth posting some code up?

[Goldberg]

Its ASP.Net (VB.Net)

Let me see if what I have just found is a solution though.

It's difficult to put the code up as it is incorrect at the moment.

[Goldberg]

OK I suppose an easier way to explain what I want is:

I have two columns in a table INFFLID and OUTFFLID. Both of these represent the ID of players in the database. What I need to do is bring back the results from a query on one line with the Players Names.
Currently it will only pull back the Players Names on two rows.... But I need it on one.

[Goldberg]

This is the Search String I am currently using:

SELECT [TRANSFERS].[SUCCESS], [TRANSFERS].[OUTMANCLUB], [TRANSFERS].[DATEWONLOST], [TRANSFERS].[OUTFFLID], [TRANSFERS].[INFFLID], [TRANSFERS].[BIDMID], [TRANSFERS].[TID], [TRANSFERS].[DATEIN], [TRANSFERS].[MINBID], [TRANSFERS].[FINE], [TRANSFERS].[INCLUB], [TRANSFERS].[WINBID], [PLAYERS].[PNAME], [PLAYERS].[PNAME], [TRANSFERS].[INTERMAN], [TRANSFERS].[FINEAMOUNT], [TRANSFERS].[INTERMANID], [TRANSFERS].[MAXBID] FROM [TRANSFERS], [PLAYERS] WHERE ((([PLAYERS].[PID] = [TRANSFERS].[INFFLID]) OR ([TRANSFERS].[OUTFFLID] = [PLAYERS].[PID])) AND ([TRANSFERS].[BIDMID] = '26') AND ([TRANSFERS].[SUCCESS] = 'p'))

Which gives:

p NULL NULL 14416 14493 26 200 2008-10-21 18:54:40.883 250000.00 p NULL NULL Jeremie Aliadiere Jeremie Aliadiere n NULL NULL 250000.00
p NULL NULL 14416 14493 26 200 2008-10-21 18:54:40.883 250000.00 p NULL NULL David Di Michele David Di Michele n NULL NULL 250000.00

So what I am looking for is this to be on a single row. The Player name is been repeated.

[cassy34]

I think you will need to join the table to itself. I'll try and give you an shortened form...

SELECT GOLDBERG.[OUTFFLID], CASSY34.[INFFLID] FROM
[TRANSFERS] GOLDBERG INNER JOIN [TRANSFERS] CASSY34 ON
GOLDBERG.[TID] = CASSY34.[TID]

I'm assuming that they will have the same TID value.

This will join the table to itself (and give you one record. Add in the other columns and where clauses as necessary.

Hope it helps, I'll keep looking in this afternoon if you need anything else.

[Goldberg]

I think you will need to join the table to itself. I'll try and give you an shortened form...

SELECT GOLDBERG.[OUTFFLID], CASSY34.[INFFLID] FROM
[TRANSFERS] GOLDBERG INNER JOIN [TRANSFERS] CASSY34 ON
GOLDBERG.[TID] = CASSY34.[TID]

I'm assuming that they will have the same TID value.

This will join the table to itself (and give you one record. Add in the other columns and where clauses as necessary.

Hope it helps, I'll keep looking in this afternoon if you need anything else.

I'll try this now.

[fevernova]

same your not using oracle i might of been able to help

SELECT column.table, column.table
from table, table
where column.table = column.table;

I think this is what your trying to do. I have to admit i didnt read all of the above as i havent used sql inside of vb.net yet! its currently apart of my next project

[Goldberg]

Cheers mate, not got back onto it yet, found a workaround lol