SQL Query Help

[Blackvault]

Hello

I need a little help with my sql query.

I have two tables one being longer than the other and I would like where the driverNo1 - 6 are in Table 1 and are the drivers of person Peter get the race1session1 number

This is my query so far but for some reason it returns everything. I have tried different variations of it and in one case it pulled back 6 copies of each driver

select driverName from Table1, selection where driverName = selection.driverNo1 or selection.driverNo2 and playerName = 'Peter'

These are my tables:

Table 1

	driverName	race1session1
	Sebastian Vettel	1
	Mark Weber	2
	Lewis Hamilton	3
	Jenson Button	4
	Fernando Alonso	5
	Felipe Massa	6
	Michael Sch	7
	Nico Rosberg	8
	Robert Kubica	9
	Vitaly Petrov	10
	Rubens Barrichello	11

Table 2

playerName	driverNo1	driverNo2	driverNo3	driverNo4	driverNo5	driverNo6
Peter	Fernando Alonso	Segio Perez	Jaime Alguersuari	Heikki Kovalainen	Jarno Trulli	Rubens Barrichello
Brian	Sebastian Vettel	Narain Karthikeyan	Jarno Trulli	Jenson Button	Kamui Kobayashi	Segio Perez
Keith	Sebastien Buemi	Jarno Trulli	Jaime Alguersuari	Kamui Kobayashi	Heikki Kovalainen	Narain Karthikeyan
Paul	Jenson Button	Sebastien Buemi	Jarno Trulli	Jaime Alguersuari	Heikki Kovalainen	Narain Karthikeyan
Aaron	Rubens Barrichello	Paul di Resta	Sebastien Buemi	Jaime Alguersuari	Heikki Kovalainen	Narain Karthikeyan
Dave	Narain Karthikeyan	Heikki Kovalainen	Jerome d'Ambrosio	Timo Glock	Jarno Trulli	Jaime Alguersuari

Any help in this matter is appericated. This is not for school/college/uni work.

Thanks,

Peter

 

[rpstewart]

Certainly start with adding some brackets to make it clear where the ands and ors sit

where (driverName = selection.driverNo1 or selection.driverNo2) and

 

[GravyMonster]

I have two tables one being longer than the other and I would like where the driverNo1 - 6 are in Table 1 and are the drivers of person Peter get the race1session1 number

I understand those words, but not in that order.

What is it you're trying to do?

Looks like a fantasy F1 site? I was going to do one of those this year too

 

[Blackvault]

I understand those words, but not in that order.

What is it you're trying to do?

Looks like a fantasy F1 site? I was going to do one of those this year too

I would like find only one person's driver selections in the results table and pick out the girdposition

Yes it is. Got a fair chunk of it done.

Regards,

Blackvault

 

[Pho]

I might have misunderstood this, but in table2 don't link directly to player and drivers' names, link to their ID instead.

Then you can run something like this:
SELECT * FROM table2
INNER JOIN table1
ON table1.ID = table2.driverID
WHERE table2.PlayerID = 1 -- (the ID of the Peter user)


Good tutorial on database normalisation: http://www.phlonx.com/resources/nf3/

 

[uv]

Before anything else, replace the Driver Name data in table2 with the driver IDs.
Otherwise, it's pointless even having the ID column in table1.

Secondly, you need to bracket your AND/OR statements, as per rpstewarts advice.

 

[Blackvault]

Hi Folks.

Sorry looking at my tables I shouldn't have included the ID numbers for the Table1. THose are actually the autoNumbers in the table so they don't relate to anything really. Does that affect the code you have suppiled?

Thanks,

Blackvault

 

[Pho]

Ideally you want to use those auto numbers instead of writing the text in the columns of table2 directly.

For example, let's say you have 'Jenson Button' appearing in several rows for several different users - what happens if you realised you spelt Jenson Button wrong? You need to then update each occurrence to the new name. You really want to have a table for 'drivers' and then associate the auto number (ID) of each driver to the user->driver mappings table (table2). Then if you need to update the name you only have to do it once.

Or, what happens if there are two drivers named Jenson Button, how would you differentiate them using your method? .

Have a read over the link I posted earlier .